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?

4

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.