I am trying to query a categories table that contain columns id, name, parent_id, and children where the children column is an array of object that consists of the same columns (id, name, parent_id, and children).

So far my query is this

with recursive
d as (
    select id, name, parent_id, d.children
    from categories as c
    left join lateral (
        select jsonb_agg(jsonb_build_object('id', ch.id, 'name', ch.name, 'children', '[]'::jsonb)) as children
        from categories as ch
        where ch.parent_id = c.id
    ) as d on true
    union all
    select ch.id, ch.name, ch.parent_id, ('[]'::jsonb || d.children) as children
    from categories ch, d
    where ch.id = d.parent_id
)

select c.id, c.name, d.children
from categories as c
left join lateral (
    select jsonb_agg(d.*) as children
    from d
    where d.parent_id = c.id
) as d on true
where c.parent_id is null;

And the result is close, except I only got the children of the parents. The children of children column is empty.

I have a table structure that looks like this (simplified version of my actual table)

The results I want should look like this.

[
  {
    id: 1,
    name: 'Automobile',
    parent_id: null,
    children: [
      {
        id: 2,
        name: 'Cameras',
        parent_id: 1,
        children: [
          {
            id: 3,
            name: 'Dash Cams',
            parent_id: 2,
            children: [
              {
                id: 4,
                name: '1080p',
                parent_id: 3,
                children: [
                  {
                    id: 5,
                    name: 'Front Cam',
                    parent_id: 4,
                    children: []
                  },
                  {
                    id: 6,
                    name: 'Rear Cam',
                    parent_id: 4,
                    children: []
                  }
                ]
              }
            ]
          }
        ]
      }
    ]
  }
]

If Front Cam and Rear Cam have children, it’ll return those. Basically infinitely until no more children is returned.

What am I doing wrong in my recursive query and what do I need to do to get the results I want?