Created
August 22, 2020 16:41
-
-
Save michelp/0be6266809eeea94e59d6859e4db8c34 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment