horrible.sql

WITH RECURSIVE t AS
(
    SELECT id, '{}'::int[] AS parents, 0 AS level
    FROM z_msg WHERE in_reply_to IS NULL
UNION ALL
    SELECT c.id, parents || c.in_reply_to, level+1
    FROM t JOIN z_msg AS s c ON t.id = c.in_reply_to
),
t2 AS
(
    SELECT c.in_reply_to, json_agg(jsonb_build_object('User', name, 'Comment', c.comment, 'Replies', '{}'::json[]))::jsonb AS js
    FROM t JOIN z_msg AS c ON t.id = c.id
    JOIN z_user ON z_user.id = c.user_id
    WHERE level > 0 AND NOT c.id = ANY(parents)
    GROUP BY c.in_reply_to
UNION ALL
    SELECT c.in_reply_to, jsonb_build_object('Name', name, 'Comment', c.comment) || jsonb_build_object('Replies', js) AS js
    FROM t2 JOIN z_msg AS c ON c.id = t2.in_reply_to
    JOIN z_user ON z_user.id = c.user_id 
)
SELECT jsonb_agg(js)
FROM t2
WHERE in_reply_to IS NULL;