Postgres 9.6

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

CREATE TABLE users (
    id int,
    name text
);

INSERT INTO users VALUES
(1, 'Mike Tyson');

CREATE TABLE news (
   id int,
   title text,
   body text
);

INSERT INTO news VALUES 
(1, 'Hello world', 'Welcome to our world'),
(2, 'Whats next', 'A quick guide on what to do next');

CREATE TABLE comments (
    id text,
    body text,
    user_id int,
    likes int,
    news_id int
);

INSERT INTO comments VALUES
('a', 'foo', 1, 1, 2),
('b', 'foofoo', 1, 232, 2),
('c', 'foofoofoo', 1, 23232, 2),
('d', 'fooFOO', 1, 53, 2),
('e', 'cookies', 1, 864, 1),
('f', 'bar', 1, 44, 1),
('g', 'barbar', 1, 54, 1),
('h', 'barBAR', 1, 222, 1),
('i', 'more cookies', 1, 1, 1);

CREATE TABLE replies (
    id text,
    parent_id text
);

INSERT INTO replies VALUES
('a', NULL),
('b', 'a'),
('c', 'b'),
('d', 'a'),
('e', NULL),
('f', NULL),
('g', 'f'),
('h', 'f'),
('i', NULL);
1 rows affected

2 rows affected

9 rows affected

9 rows affected

        WITH RECURSIVE parent_tree AS (
            SELECT 
                r.id, 
                NULL::text[] as parent_id,
                array_append('{comments}'::text[], (row_number() OVER ())::text) as path, 
                rc.children  
            FROM replies r
            LEFT JOIN LATERAL (SELECT parent_id, ARRAY_AGG(replies.id) as children FROM replies
            INNER JOIN comments ON comments.id = replies.id
            WHERE parent_id = r.id AND comments.news_id=2
            GROUP BY parent_id) rc ON rc.parent_id = r.id
            INNER JOIN comments ON r.id = comments.id
            WHERE r.parent_id IS NULL AND comments.news_id=2
        
            UNION
            
            SELECT 
                r.id, 
                array_append(pt.parent_id, r.parent_id), 
                array_append(array_append(pt.path, 'children'), (row_number() OVER (PARTITION BY pt.parent_id))::text),
                rc.children      
            FROM parent_tree pt
            JOIN replies r ON r.id = ANY(pt.children)
            LEFT JOIN LATERAL (SELECT parent_id, ARRAY_AGG(id) as children FROM replies WHERE parent_id = r.id GROUP BY parent_id) rc ON rc.parent_id = r.id
        ), json_objects AS (
           SELECT c.id, jsonb_build_object('children', '[]'::jsonb, 'comment_id', c.id, 'username', u.name, 'comment_body', c.body, 'comment_likes', c.likes) as jsondata
           FROM comments c
           LEFT JOIN users u ON u.id = c.user_id
        )
        SELECT 
            parent_id, 
            path,
            jsondata
        FROM parent_tree pt 
        LEFT JOIN json_objects jo ON pt.id = jo.id
        ORDER BY parent_id NULLS FIRST


parent_id
path
jsondata


null
{comments,1}
{"children": [], "username": "Mike Tyson", "comment_id": "a", "comment_body": "foo", "comment_likes": 1}

{a}
{comments,1,children,1}
{"children": [], "username": "Mike Tyson", "comment_id": "b", "comment_body": "foofoo", "comment_likes": 232}

{a}
{comments,1,children,2}
{"children": [], "username": "Mike Tyson", "comment_id": "d", "comment_body": "fooFOO", "comment_likes": 53}

{a,b}
{comments,1,children,1,children,1}
{"children": [], "username": "Mike Tyson", "comment_id": "c", "comment_body": "foofoofoo", "comment_likes": 23232}


CREATE OR REPLACE FUNCTION json_tree() RETURNS jsonb AS $$
DECLARE
    _json_output jsonb;
    _temprow record;
BEGIN

    SELECT 
        jsonb_build_object('comments', '[]'::jsonb) 
    INTO _json_output;

    FOR _temprow IN
        WITH RECURSIVE parent_tree AS (
            SELECT 
                r.id, 
                NULL::text[] as parent_id,
                array_append('{comments}'::text[], (row_number() OVER ())::text) as path, 
                rc.children  
            FROM replies r
            LEFT JOIN LATERAL (SELECT parent_id, ARRAY_AGG(id) as children FROM replies WHERE parent_id = r.id GROUP BY parent_id) rc ON rc.parent_id = r.id
            INNER JOIN comments c ON c.id = r.id
            WHERE r.parent_id IS NULL AND c.news_id=2
        
            UNION
            
            SELECT 
                r.id, 
                array_append(pt.parent_id, r.parent_id), 
                array_append(array_append(pt.path, 'children'), (row_number() OVER (PARTITION BY pt.parent_id))::text),
                rc.children      
            FROM parent_tree pt
            JOIN replies r ON r.id = ANY(pt.children)
            LEFT JOIN LATERAL (SELECT parent_id, ARRAY_AGG(id) as children FROM replies WHERE parent_id = r.id GROUP BY parent_id) rc ON rc.parent_id = r.id
        ), json_objects AS (
           SELECT c.id, jsonb_build_object('children', '[]'::jsonb, 'comment_id', c.id, 'username', u.name, 'comment_body', c.body, 'comment_likes', c.likes) as jsondata
           FROM comments c
           LEFT JOIN users u ON u.id = c.user_id
        )
        SELECT 
            parent_id, 
            path,
            jsondata
        FROM parent_tree pt 
        LEFT JOIN json_objects jo ON pt.id = jo.id
        ORDER BY parent_id NULLS FIRST
    LOOP
        SELECT jsonb_insert(_json_output, _temprow.path, _temprow.jsondata) INTO _json_output;
    END LOOP;

    RETURN _json_output;
END;
$$ LANGUAGE plpgsql;
SELECT jsonb_pretty(json_tree) FROM json_tree()


jsonb_pretty


{
    "comments": [
        {
            "children": [
            ],
            "username": "Mike Tyson",
            "comment_id": "a",
            "comment_body": "foo",
            "comment_likes": 1
        }
    ]
}

parent_id	path	jsondata
null	{comments,1}	{"children": [], "username": "Mike Tyson", "comment_id": "a", "comment_body": "foo", "comment_likes": 1}
{a}	{comments,1,children,1}	{"children": [], "username": "Mike Tyson", "comment_id": "b", "comment_body": "foofoo", "comment_likes": 232}
{a}	{comments,1,children,2}	{"children": [], "username": "Mike Tyson", "comment_id": "d", "comment_body": "fooFOO", "comment_likes": 53}
{a,b}	{comments,1,children,1,children,1}	{"children": [], "username": "Mike Tyson", "comment_id": "c", "comment_body": "foofoofoo", "comment_likes": 23232}