sql - დააბრუნეთ JSON ბავშვებით postgresql-ში

მაქვს სხრელი Postgres-ში, რომელიც აბრუნებს ამ მონაცემებს

მინდა, რომ ეს მან ჩემამდე დაბრუნდეს Json-ში, შვილებთან ერთად ასე ორგანიზებული, მაგრამ ვერ მოვახერხე მისი გადაჭრა

არის გზა PostgreSQL-ში ხელმძღვანელური მოდულების მათი შვილმოდულებით დალაგებისას, მე ვურთვებ მაგალითად თუ ვერ ვხვდები

[

  {
    "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",
  }]
}
 
]

მე ვცადე SELECT array_to_json(array_agg(row_to_json(alias))) FROM (select * from my table ) alias, ეს მაძლევს JSON-ს, მაგრამ არა მისი შვილებით

პასუხი #1

თუ თქვენი ცხრილის მონაცემები მაქსიმუმ ორი დონითაა ღრმა, მაშინ საჭიროა მხოლოდ რამდენიმე არასაკვლევი ქვესავლის გამოყენება სასურველი შედეგის მისაწვდომად; თუმცა, თუ თქვენი მონაცემები n დონით ღრმაა, დაგჭირდებათ რექურსიული cte-ს გამოყენება ნესტინგის შესაქმენად:

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

თვალი ადევნეთ აქ შედეგებს იმ შეყვანილ მონაცემებზე, რომლებიც თქვენს ნიმუშის სიღრმეს ემთხვევა.

იხილეთ აქ იგივე კითხვისთვის, რომელიც გამოყენებულია მონაცემებზე, რომლების სიღრმე > 2.

პასუხი #2

ჩაატარეთ jsonb_agg() ეტაპობრივად:

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 აქ