Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.postgresql > #886

Re: building a tree with recursive and export that as json

Show all headers | View raw

On Tuesday, October 9, 2018 at 1:45:00 PM UTC+3, lbo...@gmail.com wrote:
> 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?

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

Back to comp.databases.postgresql | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

building a tree with recursive and export that as json lboc80@gmail.com - 2018-10-09 03:45 -0700
  Re: building a tree with recursive and export that as json robamman2019@gmail.com - 2019-12-30 08:21 -0800
  Re: building a tree with recursive and export that as json robamman2019@gmail.com - 2019-12-30 08:27 -0800

csiph-web