Nested leaf node

Question

I've stored a tree that contains multiple nodes. Every record in that table represents a node and its parent-node, as follows:

node_id	parent_id
A	null
B	A
C	A
D	B
E	B

As a result, the visual tree would look like this: tree-nodes

My goal is to create a function that'll hold the JSON path for every leaf in the tree. So for my current table, the result should behave as shown below:

leaf_id	json_path
C	{"name": "A", "children": [{ "name": "C", "children": [] }] }
D	{"name": "A", "children": [{ "name": "B", "children": [{ "name": "D", "children": [] }] }] }
E	{"name": "A", "children": [{ "name": "B", "children": [{ "name": "E", "children": [] }] }] }

There's already a question with a function that does the format I'm trying to achieve (link below): nested-json-object. However, the written function selects the entire tree. Therefore, as I mentioned above, I need the path of every leaf node.

Answer 1

Using a recursive cte:

with recursive cte(id, l, js) as (
   select t.node_id, t.node_id, jsonb_build_object('name', t.node_id, 'children', '[]'::jsonb) from tbl t 
   where not exists (select 1 from tbl t1 where t1.parent_id = t.node_id)
   union all
   select t3.parent_id, t3.l, jsonb_build_object('name', t3.parent_id, 'children', t3.js) 
   from (select t2.parent_id, t2.l, jsonb_agg(t2.js) js  
         from (select t1.parent_id, c.l, c.js from tbl t1 
         join cte c on c.id = t1.node_id) t2 group by t2.parent_id, t2.l) t3
)
select l leaf_id, v json_path from cte 
cross join jsonb_array_elements(js -> 'children') v where id is null

See fiddle