Postgres 13

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 items (
  item_id  serial PRIMARY KEY
, title    text NOT NULL
);

CREATE TABLE joins (
  id       serial PRIMARY KEY
, item_id  int NOT NULL REFERENCES items
, child_id int NOT NULL REFERENCES items
);

INSERT INTO items (item_id,title) VALUES
  (1,'PARENT'),
  (2,'LEVEL 2'),
  (3,'LEVEL 3.1'),
  (4,'LEVEL 4.1'),
  (5,'LEVEL 4.2'),
  (6,'LEVEL 3.2');
  
INSERT INTO joins (item_id, child_id) VALUES
  (1,2),
  (2,3),
  (3,4),
  (3,5),
  (2,6);

CREATE TABLE



CREATE TABLE



INSERT 0 6



INSERT 0 5


-- Iterative aggregation with pure SQL
-- Max 4 levels deep
WITH RECURSIVE path AS (
   SELECT ARRAY[item_id, child_id] AS path, child_id AS item_id, 2 AS lvl
   FROM   joins
   WHERE  item_id = 1  -- root item_id HERE
   
   UNION ALL
   SELECT p.path || j.child_id, j.child_id, p.lvl + 1
   FROM   path  p
   JOIN   joins j ON j.item_id = p.item_id
   WHERE  p.lvl < 7  -- HARD limit
   )
, tree AS (
   SELECT p.*, to_jsonb(i) AS item
   FROM   path p
   JOIN   items i USING (item_id)
   ORDER  BY lvl, item_id
   )
, lvl4 AS (  -- leaf!
   SELECT path[3] AS item_id, jsonb_agg(item) AS children
   FROM   tree
   WHERE  lvl = 4
   GROUP  BY 1
   )
,  lvl3 AS (
   SELECT path[2] AS item_id
        , jsonb_agg(jsonb_set_lax(item, '{children}', children, true, 'return_target')) AS children
   FROM   tree t
   LEFT   JOIN lvl4 USING (item_id)
   WHERE  lvl = 3
   GROUP  BY 1
   )
,  lvl2 AS ( -- stem!
   SELECT jsonb_agg(jsonb_set_lax(item, '{children}', children, true, 'return_target')) AS children
   FROM   tree t
   LEFT   JOIN lvl3 USING (item_id)
   WHERE  lvl = 2
   )
SELECT i.item_id
     , jsonb_pretty(jsonb_set_lax(to_jsonb(i), '{children}', children, true, 'return_target')) AS tree
FROM   items i
LEFT   JOIN lvl2 ON true
WHERE  item_id = 1;  -- root item_id HERE


item_id
tree


1
{
    "title": "PARENT",
    "item_id": 1,
    "children": [
        {
            "title": "LEVEL 2",
            "item_id": 2,
            "children": [
                {
                    "title": "LEVEL 3.1",
                    "item_id": 3,
                    "children": [
                        {
                            "title": "LEVEL 4.1",
                            "item_id": 4
                        },
                        {
                            "title": "LEVEL 4.2",
                            "item_id": 5
                        }
                    ]
                },
                {
                    "title": "LEVEL 3.2",
                    "item_id": 6
                }
            ]
        }
    ]
}



SELECT 1


-- Max 7 levels deep: to demonstrate how to extrapolate ...
WITH RECURSIVE path AS (
   SELECT ARRAY[item_id, child_id] AS path, child_id AS item_id, 2 AS lvl
   FROM   joins
   WHERE  item_id = 1  -- root item_id HERE
   
   UNION ALL
   SELECT p.path || j.child_id, j.child_id, p.lvl + 1
   FROM   path  p
   JOIN   joins j ON j.item_id = p.item_id
   WHERE  p.lvl < 7  -- HARD limit
   )
, tree AS (
   SELECT p.*, to_jsonb(i) AS item
   FROM   path p
   JOIN   items i USING (item_id)
   ORDER  BY lvl, item_id
   )
, lvl7 AS (  -- leaf!
   SELECT path[6] AS item_id, jsonb_agg(item) AS children
   FROM   tree
   WHERE  lvl = 7
   GROUP  BY 1
   )
,  lvl6 AS (
   SELECT path[5] AS item_id
        , jsonb_agg(jsonb_set_lax(item, '{children}', children, true, 'return_target')) AS children
   FROM   tree t
   LEFT   JOIN lvl7 USING (item_id)
   WHERE  lvl = 6
   GROUP  BY 1
   )
,  lvl5 AS (
   SELECT path[4] AS item_id
        , jsonb_agg(jsonb_set_lax(item, '{children}', children, true, 'return_target')) AS children
   FROM   tree t
   LEFT   JOIN lvl6 USING (item_id)
   WHERE  lvl = 5
   GROUP  BY 1
   )
,  lvl4 AS (
   SELECT path[3] AS item_id
        , jsonb_agg(jsonb_set_lax(item, '{children}', children, true, 'return_target')) AS children
   FROM   tree t
   LEFT   JOIN lvl5 USING (item_id)
   WHERE  lvl = 4
   GROUP  BY 1
   )
,  lvl3 AS (
   SELECT path[2] AS item_id
        , jsonb_agg(jsonb_set_lax(item, '{children}', children, true, 'return_target')) AS children
   FROM   tree t
   LEFT   JOIN lvl4 USING (item_id)
   WHERE  lvl = 3
   GROUP  BY 1
   )
,  lvl2 AS ( -- stem!
   SELECT jsonb_agg(jsonb_set_lax(item, '{children}', children, true, 'return_target')) AS children
   FROM   tree t
   LEFT   JOIN lvl3 USING (item_id)
   WHERE  lvl = 2
   )
SELECT i.item_id
     , jsonb_pretty(jsonb_set_lax(to_jsonb(i), '{children}', children, true, 'return_target')) AS tree
FROM   items i
LEFT   JOIN lvl2 ON true
WHERE  item_id = 1;  -- root item_id HERE


item_id
tree


1
{
    "title": "PARENT",
    "item_id": 1,
    "children": [
        {
            "title": "LEVEL 2",
            "item_id": 2,
            "children": [
                {
                    "title": "LEVEL 3.1",
                    "item_id": 3,
                    "children": [
                        {
                            "title": "LEVEL 4.1",
                            "item_id": 4
                        },
                        {
                            "title": "LEVEL 4.2",
                            "item_id": 5
                        }
                    ]
                },
                {
                    "title": "LEVEL 3.2",
                    "item_id": 6
                }
            ]
        }
    ]
}



SELECT 1