By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3601421 fiddles created (48000 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
✓
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":[]}
…
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;
