title: How to output nested json from nested CTE in postgresql using python?
body: <p>Table defined using sqlalchemy core: </p>
  
  <pre><code>categories = Table("categories", metadata,
                     Column("id", Integer, primary_key=True),
                     Column("name", String),
                     Column("parent_id", Integer, 
                            ForeignKey("categories.id"),
                     Column("dept_id", Integer, 
                            ForeignKey("departments.id"),
                            CheckConstraint('id!=parent_id'), 
                            nullable=True),
                     UniqueConstraint('parent_id','name', 
                     name='parent_category'))
  </code></pre>
  
  <p>Expected o/p:</p>
  
  <pre><code>{"id": 1,
  "parent_id": null,
  "name": "root",
  "dept_id": null,
     children:[
     {id": 2, "parent_id": 1, "name": "Top_level", "dept_id": null},
     {"id": 3, "parent_id": 1, "name": "Rt_Docs", "dept_id": null}}
     ]
  }]
  </code></pre>
  
  <p>What I tried using sqlalchemy:</p>
  
  <p>s = text('with recursive rec_cat as(select id, parent_id, name, dept_id, 0 as level, array[id] as path_info from categories where parent_id is null union all select cat1.id, cat1.parent_id, cat1.name, cat1.dept_id, level + 1, path_info||cat1.parent_id from rec_cat p join categories cat1 on cat1.parent_id=p.id where not cat1.id= any(path_info)), dncc as (select cat1.parent_id, json_agg(jsonb_build_object(\'Name\',cat1.name))::jsonb as js from rec_cat tree join categories cat1 using (id)  where level > 0 and not id=any(path_info) group by cat1.parent_id union all select cat1.parent_id, jsonb_build_object(\'Name\', cat1.name) || jsonb_build_object(\'Sub-cat\', js) as js from dncc tree join categories cat1 on cat1.id=tree.parent_id) select jsonb_agg(js) from dncc where parent_id is null')</p>
  
  <pre><code>cat_list = []
  mdept_id = None
  rs = g.conn.execute(s)
  for r in rs:
      mx = collections.OrderedDict(r)
      cat_list.append(mx)
  #print cat_list, 'cat_list'
  return jsonify({'cat_list': cat_list}), 200
  </code></pre>
  
  <p>I am getting the output but not in "desired" hierarchical nested format. It gives the result but after each level will give full tree with subcategories  as in the link. Pl. suggest what changes I need to make to get the o/p in the format I stated above. I have tried the link: <a href="https://tapoueh.org/blog/2018/01/exporting-a-hierarchy-in-json-with-recursive-queries/#exporting-a-hierarchy-in-json" rel="nofollow noreferrer">enter link description here</a></p>
  
  <p>Result with above is :</p>
  
  <pre><code>cat_list": [
      {
        "jsonb_agg": [
          {
            "Name": "root", 
            "Sub-cat": [
              {
                "Name": "dne-mount"
              }, 
              {
                "Name": "dne-mktg"
              }, 
              {
                "Name": "Marketing"
              }, 
              {
                "Name": "Top_level"
              }, 
              {
                "Name": "Rt_Docs"
              }
            ]
          }, 
          {
            "Name": "root", 
            "Sub-cat": {
              "Name": "Top_level", 
              "Sub-cat": [
                {
                  "Name": "Cat1"
                }
              ]
  </code></pre>
  
  <p>ie. not nested list</p>