Paste this into a new question or an answer at dba.stackexchange.com:
<!-- --> > 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":[]}'); > > <pre> > ✓ > 5 rows affected > </pre> <!-- --> > -- 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; > > <pre> > p_id | data | c_id | data > ---: | :------------------------------- | ---: | :---------------------------- > 1 | {&quot;parent&quot;:null,&quot;children&quot;:[2,3]} | 2 | {&quot;parent&quot;:1,&quot;children&quot;:[4,5]} > 1 | {&quot;parent&quot;:null,&quot;children&quot;:[2,3]} | 3 | {&quot;parent&quot;:1,&quot;children&quot;:[]} > 2 | {&quot;parent&quot;:1,&quot;children&quot;:[4,5]} | 4 | {&quot;parent&quot;:2,&quot;children&quot;:[]} > 2 | {&quot;parent&quot;:1,&quot;children&quot;:[4,5]} | 5 | {&quot;parent&quot;:2,&quot;children&quot;:[]} > 3 | {&quot;parent&quot;:1,&quot;children&quot;:[]} | <em>null</em> | <em>null</em> > 4 | {&quot;parent&quot;:2,&quot;children&quot;:[]} | <em>null</em> | <em>null</em> > 5 | {&quot;parent&quot;:2,&quot;children&quot;:[]} | <em>null</em> | <em>null</em> > </pre> <!-- --> > -- 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; > > <pre> > p_id | p_data | c_id | c_data > ---: | :------------------------------- | ---: | :---------------------------- > 1 | {&quot;parent&quot;:null,&quot;children&quot;:[2,3]} | 2 | {&quot;parent&quot;:1,&quot;children&quot;:[4,5]} > 1 | {&quot;parent&quot;:null,&quot;children&quot;:[2,3]} | 3 | {&quot;parent&quot;:1,&quot;children&quot;:[]} > 2 | {&quot;parent&quot;:1,&quot;children&quot;:[4,5]} | 4 | {&quot;parent&quot;:2,&quot;children&quot;:[]} > 2 | {&quot;parent&quot;:1,&quot;children&quot;:[4,5]} | 5 | {&quot;parent&quot;:2,&quot;children&quot;:[]} > </pre> *db<>fiddle [here](https://dbfiddle.uk/?rdbms=postgres_13&fiddle=4aba27b9835d066e61e3f166857480b5)*
back to fiddle