building a tree with recursive and export that as json

[lbo...@gmail.com]

I'm having issues on building a tree starting from a table.
I'm following the tutorial from D.Fontaine:


but something is wrong in my query. here is a subset of rows from this table:
http://dpaste.com/1NE8X6G

- root is '57b2e67b-5862-499a-a471-0f2f6b23440e'
- under the root, there are two children '5c51558b-1180-495f-88c3-f7af49bafcf3' and '3962f997-9e14-4cac-a95f-dc20c077a531'
- each one of those has other children and so on.

the expected result would be:

[
{'Name': '57b2e67b-5862-499a-a471-0f2f6b23440e',
'Sub Classes': [
{'Name': '5c51558b-1180-495f-88c3-f7af49bafcf3',
'Subclasses': [

]},
{'Name': '3962f997-9e14-4cac-a95f-dc20c077a531',
'Subclasses': [
{'Name': '5c51558b-1180-495f-88c3-f7af49bafcf3',
'Subclasses': [
...
]}
]}
}
]

but it's not so I'm messing up something. here's the query:

with recursive rels_from_parents as
(
select sel.lo_id, '{}'::uuid[] as parents, sel.level as _level
from (select * from myproj_loparentrelation where root_id = '57b2e67b-5862-499a-a471-0f2f6b23440e') as sel
where sel.parent_id = sel.root_id
union all
select c.lo_id, parents || c.parent_id, c.level as _level
from rels_from_parents p
join myproj_loparentrelation c
on c.parent_id = p.lo_id
where not c.lo_id = any(parents)
),
rels_from_children as
(
select c.parent_id,
json_agg(jsonb_build_object('lo_id', c.lo_id::text))::jsonb as js
from rels_from_parents tree
join myproj_loparentrelation c using(lo_id)
where level > 0 and not lo_id = any(parents)
group by c.parent_id
union all
select c.parent_id,
jsonb_build_object('Name', c.lo_id::text)
|| jsonb_build_object('Sub Classes', js) as js
from rels_from_children tree
join myproj_loparentrelation c on c.lo_id = tree.parent_id
)
select jsonb_pretty(jsonb_agg(js))
from rels_from_children;


Can anybody help me with that?

[robamm...@gmail.com]

You don't have to make everything so complicated. Use Hypertext preprocessor for that. You can make data to objects and create recursive arrays and make last last mentioned to JSON with : json_encode() .

Kristjan Robam

[robamm...@gmail.com]

1 important thing when decoding: if you used to save arrays, then you should use function json_decode($jsonthing,true).

Kristjan Robam