Social network comments and posts model in PostgreSQL 9.6

I’m trying to figure out a data structure for migrating from MongoDB to PostgreSQL. Within my posts table in Postgres, I have a JSONB[] array holding comments for the post.

The thing is that each element in the array only holds an id of the comment author, not the actual user info of the author. What would be the best way to perform a join on every element of that array to replace the author id for their data, or if not what would be a better way to do this?

For example, one of my post table rows might look like:

id: 5
author: 1
body: "Hello word"
comments: [{author:0, body: "It's me"}]

Where the comments column is a JSONB array.

My users table might look like:

id: 0
username: Dux

I do need threaded comments.

First the author table, that’s easy.

CREATE TABLE author (
    authorid   serial  PRIMARY KEY,
    username   text    UNIQUE
);

Now we use a self-referential post table to create the hierarchical structure.

CREATE TABLE posts (
    id           serial  PRIMARY KEY,
    parent_post  int     REFERENCES posts,
    authorid     int     NOT NULL REFERENCES author,
    body         text    NOT NULL
);

Test data…

INSERT INTO author (authorid,username) VALUES
  (0, 'ecarroll'),
  (1, 'jbob');
INSERT INTO posts (id,parent_post,authorid,body) VALUES
  (0, null, 0, 'HALLO WORLD'),
  (1 ,0,    1, 'HALLO EVAN' );

Recursive query to get it working.

WITH RECURSIVE t(chain,author_un,text,id) AS (
   SELECT ARRAY[id], username, body, id
   FROM posts
   INNER JOIN author USING (authorid)
   WHERE parent_post IS NULL
   UNION ALL
     SELECT t.chain||p.id, username, p.body, p.id
     FROM t
     INNER JOIN posts AS p
       ON (t.id = p.parent_post)
     INNER JOIN author AS a
       USING (authorid)
)
SELECT *
FROM t;

 chain | author_un |    text     | id 
-------+-----------+-------------+----
 {0}   | ecarroll  | HALLO WORLD |  0
 {0,1} | jbob      | HALLO EVAN  |  1
(2 rows)

For more information on this method, see my post here

• How to build a table for a private messaging system that supports replies?