clear markdown compare help best fiddles feedback dbanow.uk
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2591678 fiddles created (45711 in the last week).

CREATE TABLE items ( item_id serial PRIMARY KEY, title text ); CREATE TABLE joins ( id serial PRIMARY KEY, item_id int, child_id int ); 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); WITH RECURSIVE t(item_id, json, level) AS ( SELECT item_id, to_jsonb(items), 1 FROM items WHERE NOT EXISTS ( SELECT 2 FROM joins WHERE items.item_id = joins.item_id ) UNION ALL SELECT parent.item_id, to_jsonb(parent) || jsonb_build_object( 'children', t.json ), level + 1 FROM t JOIN joins AS j ON t.item_id = j.child_id JOIN items AS parent ON j.item_id = parent.item_id WHERE level < 7 ) SELECT item_id, jsonb_pretty(json) FROM t WHERE item_id = 1;
6 rows affected
5 rows affected
item_id jsonb_pretty
1 { "title": "PARENT", "item_id": 1, "children": { "title": "LEVEL 2", "item_id": 2, "children": { "title": "LEVEL 3.2", "item_id": 6 } } }
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 } } } }
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.2", "item_id": 5 } } } }
 hidden batch(es)