CODEWITHSUNDEEP
postgresql
julian
julian

Reputation: 33

Postgres rows to complex json

I have two tables like this

CREATE TABLE posts (
  id PRIMARY KEY,
  text varchar
);

CREATE TABLE comments (
  id PRIMARY KEY,
  text varchar,
  post_id integer,
  reply_to integer -- recursive reference
);

i need query that turns this related data into graph like nested json like this

[
    {
        text: 'post',
        comments: [
            {
                text: 'normal comment',
                replies: [
                    { text: 'reply comment' }
                ]
            },
            { text: 'other normal comment' }   
        ]
    }
]

so far what i have is this query which gets me comments array but no replies 

select row_to_json(t)
from (
    select id, text, created_at,
        (
            select array_to_json(array_agg(row_to_json(d)))
            from (
                select id, text, post_id, replied_to, created_at
                from comments
                where post_id = posts.id and replied_to is null
            ) d
        ) as comments
    from posts
) t

but can't figure out how to query for replies too, so that i have complete tree, pls help

Upvotes: 0

Views: 90

Answers (1)

J Spratt
J Spratt

Reputation: 2012

You need to use a recursive query to follow the recursion of the ids.

SELECT row_to_json(t)
FROM (
  SELECT id, text,
    (
      SELECT array_to_json(array_agg(row_to_json(x))) AS comments 
      FROM (                               
        SELECT id, text, post_id, reply_to,
          ( SELECT array_to_json(array_agg(row_to_json(d)))
            FROM (
                    WITH RECURSIVE temp
                    AS
                    (
                      SELECT id, text, post_id, reply_to
                      FROM comments
                      WHERE reply_to IS NULL
                        AND post_id = posts.id
                        AND id = c.id
                      UNION ALL
                      SELECT nxt.id, nxt.text, nxt.post_id, nxt.reply_to
                      FROM temp prv
                      JOIN comments nxt ON prv.id = nxt.reply_to
                      WHERE nxt.reply_to IS NOT NULL
                     ) SELECT * FROM temp WHERE reply_to IS NOT NULL
                   ) d
           ) as replies
        FROM comments c
        WHERE c.reply_to IS NULL
          AND c.post_id = posts.id
      ) x
    )
  FROM posts
) t

Here is the SQLFiddle.

Semi-formatted output:

{"id":1,
 "text":"Test post",
 "comments":[{
     "id":1,
     "text":"Test comment",
     "post_id":1,
     "reply_to":null,
     "replies":[{
         "id":2,
         "text":"Test reply",
         "post_id":1,
         "reply_to":1
         },
        {"id":3,
         "text":"Reply reply",
         "post_id":1,
         "reply_to":2
        }
     ]},
     {"id":4,
      "text":"Comment 2",
      "post_id":1,
      "reply_to":null,
      "replies":[{
          "id":5,
          "text":"Reply 2",
          "post_id":1,
          "reply_to":4
      }]
    }
]}

That's a doozy.

If you are able to rename reply_to to parent_id it would describe the relationship right in the column name.

Upvotes: 1

Related Questions