By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
create table modules (id_module int, id_parent_module int, module_code text, name_module text, desc_module text);
insert into modules values (1, null, '001', 'A', 'A'),
(2, 1, '011.002', 'B', 'B'),
(3, 1, '232', 'C', 'C'),
(4, 1, 'asdf', 'asdf', 'asdf'),
(5, null, 'asdf', 'asdf', 'asdf'),
(14, 5, 'asdf', 'asdf', 'asdf'),
(6, null, '23', 'asdf', 'qw'),
(7, 6, 'asdf', 'asdf', 'asdf'),
(8, 7, 'asdf', 'asdf', 'asdf'),
(22, 7, 'asdf', 'asdf', 'asdf'),
(9, null, 'asdf', 'asdf', 'asdfsad'),
(20, 9, 'asdf', 'asdf', 'asdf'),
(21, 9, 'asdf', 'asdf', 'asdf'),
(10, null, 'asdf', 'asdf', 'asdf'),
(11, 10, 'sadf', 'asdf', 'asdf'),
(12, 11, 'asdf', 'asdf', 'asdfsf'),
(13, 11, 'asdf', 'asdf', 'asdf');
CREATE TABLE
INSERT 0 17
with recursive cte(id, p, js) as (
select t.id_module, t.id_parent_module, json_agg(t.jsn) from (select m.id_module, m.id_parent_module, json_build_object('id_module', m1.id_module, 'id_parent_module', m1.id_parent_module, 'module_code', m1.module_code, 'name_module', m1.name_module, 'desc_module', m1.desc_module) jsn from modules m join modules m1 on m1.id_parent_module = m.id_module where not exists (select 1 from modules m2 where m2.id_parent_module = m1.id_module)) t group by t.id_module, t.id_parent_module
union all
select t.id_module, t.id_parent_module, json_agg(t.jsn) from (select m.id_module, m.id_parent_module, json_build_object('id_module', m2.id_module, 'id_parent_module', m2.id_parent_module, 'module_code', m2.module_code, 'name_module', m2.name_module, 'desc_module', m2.desc_module, 'children', c.js) jsn from modules m join cte c on m.id_module = c.p join modules m2 on m2.id_module = c.id) t group by t.id_module, t.id_parent_module
)
select jsonb_pretty(t.result::jsonb) from (select json_agg(json_build_object('id_module', m2.id_module, 'id_parent_module', m2.id_parent_module, 'module_code', m2.module_code, 'name_module', m2.name_module, 'desc_module', m2.desc_module, 'children', c.js)) result from cte c join modules m2 on c.id = m2.id_module where c.p is null) t
| jsonb_pretty |
|---|
| [ { "children": [ { "id_module": 4, "desc_module": "asdf", "module_code": "asdf", "name_module": "asdf", "id_parent_module": 1 }, { "id_module": 3, "desc_module": "C", "module_code": "232", "name_module": "C", "id_parent_module": 1 }, { "id_module": 2, "desc_module": "B", "module_code": "011.002", "name_module": "B", "id_parent_module": 1 } ], "id_module": 1, "desc_module": "A", "module_code": "001", "name_module": "A", "id_parent_module": null }, { "children": [ { "id_module": 14, "desc_module": "asdf", "module_code": "asdf", "name_module": "asdf", "id_parent_module": 5 } ], "id_module": 5, "desc_module": "asdf", "module_code": "asdf", "name_module": "asdf", "id_parent_module": null }, { "children": [ { "children": [ { "id_module": 22, "desc_module": "asdf", "module_code": "asdf", "name_module": "asdf", "id_parent_module": 7 }, { "id_module": 8, "desc_module": "asdf", "module_code": "asdf", "name_module": "asdf", "id_parent_module": 7 } ], "id_module": 7, "desc_module": "asdf", "module_code": "asdf", "name_module": "asdf", "id_parent_module": 6 } ], "id_module": 6, "desc_module": "qw", "module_code": "23", "name_module": "asdf", "id_parent_module": null }, { "children": [ { "id_module": 21, "desc_module": "asdf", "module_code": "asdf", "name_module": "asdf", "id_parent_module": 9 }, { "id_module": 20, "desc_module": "asdf", "module_code": "asdf", "name_module": "asdf", "id_parent_module": 9 } ], "id_module": 9, "desc_module": "asdfsad", "module_code": "asdf", "name_module": "asdf", "id_parent_module": null }, { "children": [ { "children": [ { "id_module": 13, "desc_module": "asdf", "module_code": "asdf", "name_module": "asdf", "id_parent_module": 11 }, { "id_module": 12, "desc_module": "asdfsf", "module_code": "asdf", "name_module": "asdf", "id_parent_module": 11 } ], "id_module": 11, "desc_module": "asdf", "module_code": "sadf", "name_module": "asdf", "id_parent_module": 10 } ], "id_module": 10, "desc_module": "asdf", "module_code": "asdf", "name_module": "asdf", "id_parent_module": null } ] |
SELECT 1