postgresql How to generate nested json objects by one table-CodePudding

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

I have a table with data:

-----------------------------
| id | section | subsection |
----------------------------
| 1  | s_1     | ss_1       |
----------------------------
| 1  | s_1     | ss_2       |
----------------------------
| 1  | s_2     | ss_3       |
----------------------------
| 2  | s_3     | ss_4       |
----------------------------

I want to create json like that:

[
  {
    "id": 1,
    "sections": [
      {
        "section": "s_1",
        "subsections": [
          {
            "subsection": "ss_1"
          },
          {
            "subsection": "ss_2"
          }
        ]
      },
      {
        "section": "s_2",
        "subsections": [
          {
            "subsection": "ss_3"
          }
        ]
      }
    ]
  },
  {
    "id": 2,
    "sections": [
      {
        "section": "s_3",
        "subsections": [
          {
            "subsection": "ss_3"
          }
        ]
      }
    ]
  }
]

I tried do it like that:

select
json_build_array(
    json_build_object(
        'id', a.id,
        'sections', json_agg(
                json_build_object(
                    'section', b.section,
                    'subsections', json_agg(
                            json_build_object(
                                'subsection', c.subsection
                            )
                    )
                )
        )
    )
)
from table as a
         inner join table  as b on a.section = b.section
         inner join table  as c on b.subsection = c.subsection
group by a.id;

BUT there is a problem: Nested aggregate calls are not allowed

Is there any possible way to use nested aggregate calls? Or is there more elegant solution?

CodePudding user response：

You'll need to use CTEs or nested queries for this. No JOINs necessary though:

SELECT json_agg(
    json_build_object(
        'id', a.id,
        'sections', a.sections
    )
)
FROM (
    SELECT b.id, json_agg(
        json_build_object(
            'section', b.section,
            'subsections', b.subsections
        )
    ) AS sections
    FROM (
        SELECT c.id, c.section, json_agg(
            json_build_object(
                'subsection', c.subsection
            )
        ) AS subsections
        FROM table AS c
        GROUP BY id, section
    ) AS b
    GROUP BY id
) AS a;