I have 2 tables
CREATE TABLE parent (
id SERIAL PRIMARY KEY,
label VARCHAR
);
CREATE TABLE child (
id SERIAL PRIMARY KEY,
label VARCHAR,
child_id INTEGER,
parent_id INTEGER
);
child can have reference to itself its recursive. i want to query parents with all of its children as json arrays. for single level i have
SELECT
p.label, jsonb_agg(jsonb_build_object('id', c.id, 'label', c.label, 'child_id', c.child_id, 'parent_id', c.parent_id))
as children
FROM parent p LEFT JOIN child c on c.parent_id = p.id GROUP BY p.id
this gets me
[ { id: 1,
label: 'parent 1',
children:
[ { id: 1,
label: 'child 1',
child_id: null,
parent_id: 1 },
{ id: 2,
label: 'child 2',
child_id: 1,
parent_id: 1 } ] } ]
this works for single level, but i want all of its children, something like this
[ { id: 1,
label: 'parent 1',
children:
[ { id: 1,
label: 'child 1',
child_id: null,
parent_id: 1
children: [
{ id: 2
label: 'child 1s child'
child_id: 1,
parent_id: 1 }
] },
{ id: 3,
label: 'other child',
child_id: null,
parent_id: 1 } ] } ]
how do i query like that?