recursive common table expression comment threads

comment-threads.sql

create table "users" (
  "userId" serial,
  "username" text not null,
  primary key ("userId")
);

create table "comments" (
  "commentId" serial,
  "userId" integer not null,
  "content" text not null,
  "replyingTo" integer,
  primary key ("commentId"),
  foreign key ("replyingTo")
  references "comments" ("commentId")
);
  

insert into "users" ("username")
values ('thebearingedge'), ('timelikeclosure'), ('codingcodymiller'), ('scbowler');

insert into "comments" ("userId", "content")
values (1, 'this is cool'), (2, 'this is great'), (3, 'this is nice'), (4, 'this is awesome');

insert into "comments" ("userId", "content", "replyingTo")
values (1, 'you are nice', 3);

with recursive "thread" ("commentId", "userId", "content", "replyingTo", "replies") as (
  select "c".*,
         '[]'::json
    from "comments" as "c"
   where not exists (
     select true
       from "comments" as "r"
      where "r"."replyingTo" = "c"."commentId"
   )
  union all
  select ("c").*,
         json_agg("r") as "replies"
    from (
      select "c",
             "r"
        from "thread" as "r"
        join "comments" as "c"
          on "c"."commentId" = "r"."replyingTo"
    ) as "commentThread"
   group by "commentThread"."c"
)
select json_agg("t") as "threads"
  from "thread" as "t"
  left join "comments" as "c"
    on "c"."commentId" = "t"."replyingTo"
 where "c"."commentId" is null;

Lifted from a few Stackoverflow posts and this sandbox. Here is the result.

[
    {
        "userId": 1,
        "content": "this is cool",
        "replies": [
        ],
        "commentId": 1,
        "replyingTo": null
    },
    {
        "userId": 2,
        "content": "this is great",
        "replies": [
        ],
        "commentId": 2,
        "replyingTo": null
    },
    {
        "userId": 4,
        "content": "this is awesome",
        "replies": [
        ],
        "commentId": 4,
        "replyingTo": null
    },
    {
        "userId": 3,
        "content": "this is nice",
        "replies": [
            {
                "userId": 1,
                "content": "you are nice",
                "replies": [
                ],
                "commentId": 5,
                "replyingTo": 3
            }
        ],
        "commentId": 3,
        "replyingTo": null
    }
]