postgresql How to generate nested json objects by one table

Question

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?

Bergi · Accepted Answer · 2022-07-27 17:57:25Z

1

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;

answered Jul 27, 2022 at 17:57

Bergi

671k162 gold badges1k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

donquih0te Over a year ago

Thank you, works fine! Now | have another problem =) Sometimes result consisting ``` { "section":null, "subsections":[ { "subsection":null } ] } ``` How to filter these nulls?

Bergi Over a year ago

Add an appropriate WHERE clause to the innermost query, to ignore the rows that have NULL values in them

Collectives™ on Stack Overflow

postgresql How to generate nested json objects by one table

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related