0

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?

julian
  • 33
  • 5

0 Answers0