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.


1 Answer 1


First the author table, that's easy.

    authorid   serial  PRIMARY KEY,
    username   text    UNIQUE

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

    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
     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)

 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.