I have a table in Postgres that returns this data

I would like it to be returned to me in a Json ordered with its children as follows, but I have not been able to solve it

Is there a way in postgresql to order the parent modules with their child modules, I attach an example if I don't understand

[

  {
    "id_module": 1,
    "id_parent_module": null,
    "module_code": "001.",
    "name_module": "Atoridad Fiscal",
    "desc_module": "Atoridad Fiscal",
    "children": [{
            "id_module": 2,
            "id_parent_module": 1,
            "module_code": "001.002",
            "name_module": "Recibidos",
            "desc_module": "Recibidos",
        },
        {
          "id_module": 3,
          "id_parent_module": 1,
          "module_code": "001.003.",
          "name_module": "Enviados",
          "desc_module": "Enviados",
      },
      {
        "id_module": 4,
        "id_parent_module": 1,
        "module_code": "001.004.",
        "name_module": "Archivados",
        "desc_module": "Archivados",
    }]
},
{
  "id_module": 5,
  "id_parent_module": null,
  "module_code": "005.",
  "name_module": "Configuraciones",
  "desc_module": "Configuraciones",
  "children": [{
          "id_module": 14,
          "id_parent_module": 5,
          "module_code": "005.14.",
          "name_module": "Medios Contacto",
          "desc_module": "Medios Contacto",
      }]
},
{
  "id_module": 5,
  "id_parent_module": null,
  "module_code": "005.",
  "name_module": "Configuraciones",
  "desc_module": "Configuraciones",
  "children": [{
          "id_module": 14,
          "id_parent_module": 5,
          "module_code": "005.14.",
          "name_module": "Medios Contacto",
          "desc_module": "Medios Contacto",
      }]
},
{
  "id_module": 6,
  "id_parent_module": null,
  "module_code": "006.",
  "name_module": "Mensajes",
  "desc_module": "Bandeja del contribuyente",
  "children": [{
          "id_module": 7,
          "id_parent_module": 6,
          "module_code": "006.007.",
          "name_module": "Recibidos",
          "desc_module": "Recibidos",
      },
      {
        "id_module": 8,
        "id_parent_module": 6,
        "module_code": "006.008.",
        "name_module": "Enviados",
        "desc_module": "Enviados",
    },
    {
      "id_module": 22,
      "id_parent_module": 6,
      "module_code": "006.0022.",
      "name_module": "Buscador de Mensajes",
      "desc_module": "Buscador de Mensajes",
  }]
}
 
]

I have tried with SELECT array_to_json(array_agg(row_to_json(alias))) FROM (select * from my table ) alias it returns me the json but not with its children

CodePudding user response：

If your table data is at most two levels deep, then you only need to use several non-recursive subqueries to produce the desired result; however, if your data is n levels deep, you will need to use a recursive cte to build up the nesting:

with recursive cte(id, p, js) as (
   select t.id_module, t.id_parent_module, json_agg(t.jsn) 
   from (select m.id_module, m.id_parent_module, 
             json_build_object('id_module', m1.id_module, 
                'id_parent_module', m1.id_parent_module, 
                 'module_code', m1.module_code, 
                 'name_module', m1.name_module, 
                 'desc_module', m1.desc_module) jsn 
         from modules m join modules m1 on m1.id_parent_module = m.id_module 
         where not exists (select 1 from modules m2 where m2.id_parent_module = m1.id_module)) t  
         group by t.id_module, t.id_parent_module
   union all
   select t.id_module, t.id_parent_module, json_agg(t.jsn) 
   from (select m.id_module, m.id_parent_module, 
             json_build_object('id_module', m2.id_module, 
                'id_parent_module', m2.id_parent_module, 
                'module_code', m2.module_code, 
                'name_module', m2.name_module, 
                'desc_module', m2.desc_module, 
                'children', c.js) jsn 
         from modules m join cte c on m.id_module = c.p join modules m2 on m2.id_module = c.id) t 
         group by t.id_module, t.id_parent_module
)
select jsonb_pretty(t.result::jsonb) from (
    select json_agg(json_build_object('id_module', m2.id_module, 'id_parent_module', m2.id_parent_module, 'module_code', m2.module_code, 'name_module', m2.name_module, 'desc_module', m2.desc_module, 'children', c.js)) result 
    from cte c join modules m2 on c.id = m2.id_module where c.p is null) t

See here for results on input data of the same depth as your sample.

Also see here for the same query used on data that has a depth > 2.

CodePudding user response：

Do the jsonb_agg() in stages:

with chilluns as (
  select id_parent_module, 
         jsonb_agg(to_jsonb(my_table) order by id_module) as children
    from my_table
   where id_parent_module is not null
   group by id_parent_module
), rents as (
  select p.*, c.children
    from my_table p
         join chilluns c on c.id_parent_module = p.id_module
)
select jsonb_pretty(jsonb_agg(to_jsonb(rents) order by id_module)) as result
  from rents;

db<>fiddle here