• building a tree with recursive and export that as json

    From lboc80@gmail.com@21:1/5 to All on Tue Oct 9 03:45:00 2018
    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?

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From robamman2019@gmail.com@21:1/5 to lbo...@gmail.com on Mon Dec 30 08:21:30 2019
    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

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From robamman2019@gmail.com@21:1/5 to All on Mon Dec 30 08:27:23 2019
    1 important thing when decoding: if you used to save arrays, then you should use function json_decode($jsonthing,true).

    Kristjan Robam

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)