clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
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. 2249262 fiddles created (32197 in the last week).

CREATE TABLE test(id int PRIMARY KEY, data JSON); INSERT INTO test(id, data) VALUES (1, '{"parent":null,"children":[2,3]}') , (2, '{"parent":1,"children":[4,5]}') , (3, '{"parent":1,"children":[]}') , (4, '{"parent":2,"children":[]}') , (5, '{"parent":2,"children":[]}');
5 rows affected
 hidden batch(es)


-- include rows without children SELECT p.id AS p_id, p.data , c.id AS c_id, c.data FROM test p LEFT JOIN LATERAL json_array_elements_text(p.data->'children') pc(child) ON TRUE LEFT JOIN test c ON c.id = pc.child::int;
p_id data c_id data
1 {"parent":null,"children":[2,3]} 2 {"parent":1,"children":[4,5]}
1 {"parent":null,"children":[2,3]} 3 {"parent":1,"children":[]}
2 {"parent":1,"children":[4,5]} 4 {"parent":2,"children":[]}
2 {"parent":1,"children":[4,5]} 5 {"parent":2,"children":[]}
3 {"parent":1,"children":[]}
4 {"parent":2,"children":[]}
5 {"parent":2,"children":[]}
 hidden batch(es)


-- exclude rows without children SELECT p.id AS p_id, p.data::text AS p_data , c.id AS c_id, c.data::text AS c_data FROM test p , json_array_elements_text(p.data->'children') pc(child) LEFT JOIN test c ON c.id = pc.child::int;
p_id p_data c_id c_data
1 {"parent":null,"children":[2,3]} 2 {"parent":1,"children":[4,5]}
1 {"parent":null,"children":[2,3]} 3 {"parent":1,"children":[]}
2 {"parent":1,"children":[4,5]} 4 {"parent":2,"children":[]}
2 {"parent":1,"children":[4,5]} 5 {"parent":2,"children":[]}
 hidden batch(es)