How to create json tree from postgresql table

Question

I have the table with hierarchical data. I want to aggregate the data from one table to a complex json. The elements should be grouped by section column. And nested json should be grouped by subsection column

----------------------------------------------
| id   | section | subsection | subsubsection |
----------------------------------------------
| 111  | s_1     | null       | null          |  // root section
----------------------------------------------
| 222  | s_1     | ss_2       | null          |  // root subsection
----------------------------------------------
| 333  | s_1     | ss_2       | sss_3         |
----------------------------------------------
| 444  | s_1     | ss_2       | sss_4         |
----------------------------------------------
| 555  | s_2     | null       | null          |  // root section
----------------------------------------------
| 666  | s_2     | ss_3       | null          |  // root subsection
----------------------------------------------

I want to create json trees and save them as view

  {
    "id": 111,   
    "section": "s_1",    
    "subsections": [
      {
        "id": 222,
        "subsection": "ss_2",
        "subsections": [
          {
            "id": 333,
            "subsection": "sss_3"
          },
          {
            "id": 444,
            "subsection": "sss_4"
          }
        ]
      }
    ]
  }

What I tried to do:

create or replace view my_view(
    name
) as
SELECT 
       (
            SELECT
                json_build_object(
                        'section', a.section,
                        'subsections', a.sections
                )
            FROM (SELECT b.section,
                         json_agg(
                                 json_build_object(
                                         'subsection', b.subsection,
                                         'subsubsections', b.subsections
                                 )
                         ) AS sections
                  FROM (SELECT c.section,
                               c.subsection,
                               json_agg(
                                        json_build_object(
                                                'subsubsection', c.subsubsection
                                        )
                                   ) AS subsections
                        FROM table AS c
                        GROUP BY section, subsection
                       ) AS b
                  GROUP BY b.section) AS a
       ) AS name;

But in my solution I can't and an id to each tree node. How to add the each node id to result tree?

Ftisiot · Accepted Answer · 2022-07-28 08:40:56Z

I recreated your case with:

create table test (id int, section text, subsection text, subsubsection text);

insert into test values (111,'s_1', null, null);
insert into test values (222,'s_1', 's_2', null);
insert into test values (333,'s_1', 's_2', 's_3');
insert into test values (444,'s_1', 's_2', 's_4');
insert into test values (555,'s_5', null, null);
insert into test values (666,'s_5', 's_6', null);

Note: I changed slightly the names of the sections to be unique.

Result (Ghost is null)

 id  | section | subsection | subsubsection
-----+---------+------------+---------------
 111 | s_1     | 👻         | 👻
 222 | s_1     | s_2        | 👻
 333 | s_1     | s_2        | s_3
 444 | s_1     | s_2        | s_4
 555 | s_5     | 👻         | 👻
 666 | s_5     | s_6        | 👻
(6 rows)

Then I approached the problem in phases:

Get the list of subsubsections and related sections and subsections

SELECT c.section,
        c.subsection,
        c.subsubsection,
        json_build_object(
            'id',c.id,
            'subsubsection', c.subsubsection
        ) AS subsubsections
    FROM test AS c
    WHERE c.subsubsection is not null

Which gives

 section | subsection | subsubsection |            subsubsections
---------+------------+---------------+---------------------------------------
 s_1     | s_2        | s_3           | {"id" : 333, "subsubsection" : "s_3"}
 s_1     | s_2        | s_4           | {"id" : 444, "subsubsection" : "s_4"}
(2 rows)

Then, build subsections by joining the query above with a query specific at subsections level

SELECT c.section,
        c.subsection,
        json_build_object(
            'id',c.id,
            'subsection', c.subsection,
            'subsubsection', json_agg(subsubsections.subsubsections)
        ) AS subsections
    FROM test AS c
    left outer join subsubsections on c.section = subsubsections.section and c.subsection = subsubsections.subsection
    WHERE c.subsection is not null and c.subsubsection is null
    group by c.section,
        c.subsection,
        c.id

Note: the subsubsections table is the alias to the previous table

Result

 section | subsection |                                                             subsections
---------+------------+--------------------------------------------------------------------------------------------------------------------------------------
 s_1     | s_2        | {"id" : 222, "subsection" : "s_2", "subsubsection" : [{"id" : 333, "subsubsection" : "s_3"}, {"id" : 444, "subsubsection" : "s_4"}]}
 s_5     | s_6        | {"id" : 666, "subsection" : "s_6", "subsubsection" : [null]}
(2 rows)

Last, a query at section level, joining the results of the subsection above

SELECT c.section,
    json_build_object(
        'id',c.id,
        'section', c.section,
        'subsection', json_agg(subsections.subsections)
    ) AS subsections
FROM test AS c
left outer join subsections on c.section = subsections.section
WHERE c.subsection is null
group by c.section,
    c.id

The whole query is

WITH subsubsections as (
    SELECT c.section,
        c.subsection,
        c.subsubsection,
        json_build_object(
            'id',c.id,
            'subsubsection', c.subsubsection
        ) AS subsubsections
    FROM test AS c
    WHERE c.subsection is not null and c.subsubsection is not null
)
, subsections as (
    SELECT c.section,
        c.subsection,
        json_build_object(
            'id',c.id,
            'subsection', c.subsection,
            'subsubsection', json_agg(subsubsections.subsubsections)
        ) AS subsections
    FROM test AS c
    left outer join subsubsections on c.section = subsubsections.section and c.subsection = subsubsections.subsection
    WHERE c.subsection is not null and c.subsubsection is null
    group by c.section,
        c.subsection,
        c.id
        )
SELECT c.section,
    json_build_object(
        'id',c.id,
        'section', c.section,
        'subsection', json_agg(subsections.subsections)
    ) AS subsections
FROM test AS c
left outer join subsections on c.section = subsections.section
WHERE c.subsection is null
group by c.section,
    c.id
;

And the result is

 section |                                                                                      subsections
---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 s_1     | {"id" : 111, "section" : "s_1", "subsection" : [{"id" : 222, "subsection" : "s_2", "subsubsection" : [{"id" : 333, "subsubsection" : "s_3"}, {"id" : 444, "subsubsection" : "s_4"}]}]}
 s_5     | {"id" : 555, "section" : "s_5", "subsection" : [{"id" : 666, "subsection" : "s_6", "subsubsection" : [null]}]}
(2 rows)

Note, this will work only if section and subsection names are "unique" per subtree.

Collectives™ on Stack Overflow

How to create json tree from postgresql table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related