Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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":[]}');

CREATE TABLE



INSERT 0 5


-- 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":[]}
null
null

4
{"parent":2,"children":[]}
null
null

5
{"parent":2,"children":[]}
null
null



SELECT 7


-- 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":[]}



SELECT 4

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":[]}	null	null
4	{"parent":2,"children":[]}	null	null
5	{"parent":2,"children":[]}	null	null