Retrieve the data in JSON format including the child elements from a PostgreSQL

Question

Retrieve the data in JSON format including the child elements from a PostgreSQL

I have data from a table in Postgres that I need to be returned in Json format with its children properly ordered. So far, I haven't found a solution to achieve this. Is there a way in PostgreSQL to order the parent modules along with their child modules? Here is an example:

[
  {
    "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.014.",
      "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.022.",
        "name_module": "Buscador de Mensajes",
        "desc_module": "Buscador de Mensajes"
      }]
   }
]

I have attempted using

SELECT array_to_json(array_agg(row_to_json(alias))) FROM (select * from my table ) alias

but it only returns the JSON without organizing the data with its children.

sql json typescript postgresql

Answer 1

Answer №1

If your table data has a depth of up to two levels, you can achieve the desired outcome by utilizing multiple non-recursive subqueries. However, if your data is nested at n levels, it is necessary to employ a recursive cte to establish the hierarchy:

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

For results based on input data with similar depths as your example, refer to this link.

To see how the same query functions with data exceeding a depth of > 2, visit this page.

Answer 2

If your table data has a depth of up to two levels, you can achieve the desired outcome by utilizing multiple non-recursive subqueries. However, if your data is nested at n levels, it is necessary to employ a recursive cte to establish the hierarchy:

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

For results based on input data with similar depths as your example, refer to this link.

To see how the same query functions with data exceeding a depth of > 2, visit this page.

Answer 3

Answer №2

Execute the jsonb_agg() function in phases:


with kids as (
  select id_parent_module, 
         jsonb_agg(to_jsonb(my_data) order by id_module) as children
    from my_data
   where id_parent_module is not null
   group by id_parent_module
), parents as (
  select p.*, c.children
    from my_data p
         join kids c on c.id_parent_module = p.id_module
)
select jsonb_pretty(jsonb_agg(to_jsonb(parents) order by id_module)) as final_result
  from parents;

Check out the db<>fiddle here

Answer 4

Execute the jsonb_agg() function in phases:


with kids as (
  select id_parent_module, 
         jsonb_agg(to_jsonb(my_data) order by id_module) as children
    from my_data
   where id_parent_module is not null
   group by id_parent_module
), parents as (
  select p.*, c.children
    from my_data p
         join kids c on c.id_parent_module = p.id_module
)
select jsonb_pretty(jsonb_agg(to_jsonb(parents) order by id_module)) as final_result
  from parents;

Check out the db<>fiddle here

Retrieve the data in JSON format including the child elements from a PostgreSQL

Answer №1

Answer №2

Similar questions

Utilizing Android to Retrieve JSON Data from a Web Address

What is the best way to extract a value from a JSON object?

Uniform JSON format in REST API

Utilizing AngularJS filter in JavaScript without AngularJS Framework

Tips for fixing the issue "Uncaught SyntaxError: Unexpected token S in JSON at position 0"

How to incorporate a popup modal in your project and where should you place the DialogService constructor

Regular expression for substituting pairs of keys and values within JSON data

Is there a way to verify if the JSON Object array includes the specified value in an array?

Customize the theme type with @mui/system

SQL: Retrieve the initial instance only

What is the process for assigning a value of type NSURL to type UIimage?

The module "node_modules/puppeteer/lib/types" does not contain the export "Cookie"

Exploring the process of transforming a JSON array into separate JSON objects using Node.js

Text box accompanied by an Ext JS Combo box

Display real-time information fetched from sessionStorage (in JSON format) on a Listview widget

Display identical text using JavaScript filter

Issue: "The argument provided must be a specific string, not a string or an array of strings."

What is the best way to invoke a function that is declared within a React component in a TypeScript class?

Ways to capture targeted requests

Data from the session is not displayed when a redirect occurs