gistfile1.txt

create table test (
	id serial primary key,
	parent_id integer references test(id) default null  -- null is a root
	);

insert into test (id, parent_id) values (1, null);
insert into test (id, parent_id) values (2, 1);
insert into test (id, parent_id) values (3, 2);
insert into test (id, parent_id) values (4, 2);
insert into test (id, parent_id) values (5, 2);
insert into test (id, parent_id) values (6, 3);
insert into test (id, parent_id) values (7, 3);

with root (id) as (select id from test where parent_id is null)
select jsonb_build_object('id', root.id) from root;

with recursive root as
(
      select id, '{}'::int[] as parents, 0 as level
        from test
       where parent_id is NULL

   union all

      select c.id, parents || c.parent_id, level + 1
        from root p
             join test c
               on c.parent_id = p.id
       where not c.id = any(parents)
),
    children as
(
     select c.parent_id,
           json_agg(jsonb_build_object('id', c.id))::jsonb as js
	       from root tree
            join test c using(id)
      where level > 0 and not id = any(parents)
   group by c.parent_id

  union all
     select c.parent_id,
       jsonb_build_object('id', c.id)
       || jsonb_build_object('children', js) as js
       from children tree
            join test c on c.id = tree.parent_id
)
select jsonb_pretty(jsonb_agg(js))
  from children
 where parent_id IS NULL;