How to create json tree from postgresql table-CodePudding

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?

CodePudding user response：

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     |