return JSON with children postgresql

user_8351012 0
Viewed 81

I have a table in Postgres that returns this dataenter image description here

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

typescript postgresql sql json
2 Answers

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.

user_73267380

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

user_138083190
Related