with recursive tree as (
  select obj,
         sub,
         array[obj] as all_ancestors,
         array[knot_name] as ancestor_names
  from bounds left join knots on obj=knot_id
  where sub=1 and predicate='is_parent'

  union all

  select c.obj,
         c.sub,
         p.all_ancestors||c.obj,
         p.ancestor_names || knot_name
  from bounds c
     join tree p
      on c.sub = p.obj
     and c.obj <> ALL (p.all_ancestors) -- avoids endless loops
     left join knots on c.obj=knot_id
)
select all_ancestors,ancestor_names
from tree order by all_ancestors;

Kastalia Task 0

1587592587 / AE500422

please provide an algorithm which can transform lists provided by kastaliarbre.sql


 {6301,6302}                                                  | {Presentations,OEB.global}

 {6301,6302,6303}                                             | {Presentations,OEB.global,"Order of the day"}

 {6301,6302,6304}                                             | {Presentations,OEB.global,"All which shall be presented is work-in-progress."}

 {6301,6302,6305}                                             | {Presentations,OEB.global,"Reform Pedagogy"}

 {6301,6302,6305,6306}                                        | {Presentations,OEB.global,"Reform Pedagogy","Waldorf Pedagogy"}

 {6301,6302,6305,6307}                                        | {Presentations,OEB.global,"Reform Pedagogy"," Montessori Pedagogy"}

 {6301,6302,6305,6308}                                        | {Presentations,OEB.global,"Reform Pedagogy","Freinet Pedagogy"}

 {6301,6302,6309}                                             | {Presentations,OEB.global," Commonalities among Waldorf / Montessori / Freinet"}

 {6301,6302,6310} 



into jsons having form


var treeData = [

  {

    "name": "Top Level",

    "parent": "null",

    "children": [

      {

        "name": "Level 2: A",

        "parent": "Top Level",

        "children": [

          {

            "name": "Son of A",

            "parent": "Level 2: A"

          },

          {

            "name": "Daughter of A",

            "parent": "Level 2: A"

          }

        ]

      },

      {

        "name": "Level 2: B",

        "parent": "Top Level"

      }

    ]

  }

];