Return recursive self joins in PostgreSQL with aggregated data

Question

I have this simple table schema:

SQL Fiddle

PostgreSQL 9.6 Schema Setup:

CREATE TABLE IF NOT EXISTS users(
  id INT NOT NULL PRIMARY KEY, email TEXT NOT NULL UNIQUE, 
  password TEXT NOT NULL, first_name TEXT NOT NULL, 
  last_name TEXT NOT NULL, is_active BOOLEAN DEFAULT FALSE, 
  is_staff BOOLEAN DEFAULT FALSE, is_superuser BOOLEAN DEFAULT FALSE
);
INSERT INTO users (
  id, email, password, first_name, last_name, 
  is_active, is_staff, is_superuser
) 
VALUES 
  (
    1, '[email protected]', '23456ses', 'John', 
    'Idogun', true, true, true
  ),
  (
    2, '[email protected]', '23456ses', 'Nelson', 
    'Idogun', true, true, true
  );
-- articles table
CREATE TABLE IF NOT EXISTS articles(
  id INT NOT NULL PRIMARY KEY, 
  fore_image TEXT NULL, 
  title TEXT NOT NULL, 
  slug TEXT NOT NULL UNIQUE, 
  content TEXT NOT NULL, 
  is_published BOOLEAN DEFAULT FALSE, 
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), 
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), 
  user_id INT NOT NULL, 
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
INSERT INTO articles (
  id, title, slug, content, is_published, 
  user_id
) 
VALUES 
  (
    1, 'Upgrading from v0.4 to v0.5 of rust rocket', 
    'Upgrading from v0.4 to v0.5 of rust rocket', 
    'Upgrading from v0.4 to v0.5 of rust rocket', 
    true, 1
  );

CREATE TABLE IF NOT EXISTS comment(
    id INT NOT NULL PRIMARY KEY,
    article_id INT NOT NULL REFERENCES articles(id) ON DELETE CASCADE,
    user_id INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    responded_to_comment_id INT NULL REFERENCES comment(id) ON DELETE CASCADE,
    content TEXT NOT NULL
);
INSERT INTO comment (id, article_id, user_id, responded_to_comment_id, content) 
VALUES 
  (1, 1, 1, NULL, 'First comment'), 
  (
    2, 1, 2, 1, 'First reply'
  ),
  (3, 1, 1, 2, 'Response to the first reply.');

Query 1:

SELECT 
    c.id AS c_id, 
    c.content AS c_content,
    json_agg(r) as replies,
    u.id AS u_id
FROM comment AS c
JOIN users AS u ON c.user_id = u.id
LEFT JOIN comment r ON r.id = c.responded_to_comment_id
WHERE c.article_id = 1
GROUP BY c.id, u.id

Results:

| c_id |                c_content |                                                                                        replies | u_id |
|------|--------------------------|------------------------------------------------------------------------------------------------|------|
|    1 |            First comment |                                                                                         [null] |    1 |
|    2 |              First reply | [{"id":1,"article_id":1,"user_id":1,"responded_to_comment_id":null,"content":"First comment"}] |    2 |
|    3 | Response to first reply. |      [{"id":2,"article_id":1,"user_id":2,"responded_to_comment_id":1,"content":"First reply"}] |    1 |

I am trying to write a query that selects comments under an article and recursively nests all comments having responded_to_comment_id inside their parent comment so I will have only one row returned:

| c_id |     c_content |   replies | u_id |
|------|---------------|-----------|------|
|    1 | First comment |     [...] |    1 |

and that the results for the replies column will be something like this:

[
  {
    "id": 2,
    "user_id": 1,
    "responded_to_comment_id": 1,
    "content": "First reply",
    "replies": [
      {
        "id": 3,
        "article_id": 1,
        "user_id": 2,
        "responded_to_comment_id": 2,
        "content": "Response to the first reply."
      }
    ]
  }
]

Only top-level comments (comments whose responded_to_comment_id is NULL) should be at the top.

Answer 1

After more research and referencing Database model for a hierarchical content, I have the following query for some of my requirements. It is not recursive though.

SELECT 
  c.id AS c_id, 
  c.article_id AS c_article_id, 
  c.responded_to_comment_id AS c_responded_to_comment_id, 
  c.content AS c_content, 
  replies, 
  u.id AS u_id, 
  u.email AS u_email, 
  u.first_name AS u_first_name, 
  u.last_name AS u_last_name, 
  u.is_active AS u_is_active, 
  u.is_staff AS u_is_staff, 
  u.is_superuser AS u_is_superuser 
FROM 
  comment c 
  LEFT JOIN (
    SELECT 
      responded_to_comment_id, 
      COALESCE(
        json_agg(
          jsonb_build_object(
            'comment', 
            row_to_json(replies), 
            'author', 
            row_to_json(u_inner)
          )
        ), 
        '[]' :: JSON
      ) AS replies 
    FROM 
      comment AS replies 
      JOIN users AS u_inner ON replies.user_id = u_inner.id 
    GROUP BY 
      responded_to_comment_id
  ) AS replies ON c.id = replies.responded_to_comment_id 
  JOIN users AS u ON c.user_id = u.id 
WHERE 
  c.responded_to_comment_id IS NULL 
  AND c.article_id = 1;

The query ensures that only top-level comments come top while others are dropped as replies.

See SQL Fiddle.