Nested aggregation with `jsonb_agg`

Question

SELECT
  s."firstName",
  jsonb_agg(
    DISTINCT jsonb_build_object(
      'yearId',
      y.id,
      'classes',
      (
        SELECT
          jsonb_agg(
            jsonb_build_object(
              'classId',
              c.id
            )
          )
        FROM
          classes AS c
        WHERE
          y.id = cy."yearId"
          AND c.id = cy."classId"
          AND s.id = cys."studentId"
      )
    )
  ) AS years
FROM
  users AS s
  LEFT JOIN "classYearStudents" AS cys ON cys."studentId" = s.id
  LEFT JOIN "classYears" AS cy ON cy.id = cys."classYearId"
  LEFT JOIN "years" AS y ON y.id = cy."yearId"
GROUP BY
  s.id

SQL Output

 firstName |                                                                                                                                                                               years                                                                                                                                                                                
-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Jarrell   | [{"yearId": "bd5b69ac-6638-4d3e-8a52-94c24ed9a039", "classes": [{"classId": "2590b596-e894-4af5-8ac5-68d109eee995"}]}, {"yearId": "bd5b69ac-6638-4d3e-8a52-94c24ed9a039", "classes": [{"classId": "fe4a11f2-5f38-4f7a-bbce-609bc7ad8f99"}]}]
 Kevon     | [{"yearId": "7f5789b5-999e-45e4-aba4-9f45b29a69ef", "classes": [{"classId": "c8cda7d1-7321-443c-b0ad-6d18451613b5"}]}, {"yearId": "bd5b69ac-6638-4d3e-8a52-94c24ed9a039", "classes": [{"classId": "2590b596-e894-4af5-8ac5-68d109eee995"}]}, {"yearId": "bd5b69ac-6638-4d3e-8a52-94c24ed9a039", "classes": [{"classId": "fe4a11f2-5f38-4f7a-bbce-609bc7ad8f99"}]}]
 Antone    | [{"yearId": "7f5789b5-999e-45e4-aba4-9f45b29a69ef", "classes": [{"classId": "c8cda7d1-7321-443c-b0ad-6d18451613b5"}]}, {"yearId": "bd5b69ac-6638-4d3e-8a52-94c24ed9a039", "classes": [{"classId": "2590b596-e894-4af5-8ac5-68d109eee995"}]}, {"yearId": "bd5b69ac-6638-4d3e-8a52-94c24ed9a039", "classes": [{"classId": "fe4a11f2-5f38-4f7a-bbce-609bc7ad8f99"}]}]
(3 rows)

The problem

What I wanted was for the years with the same ID to be merged together and have multiple classes per year id. As you can see bd5b69ac-6638-4d3e-8a52-94c24ed9a039 on the first row (Jarell) has two entries in the year's column array with each having one class.

Current JSON output

[
  {
    "yearId": "bd5b69ac-6638-4d3e-8a52-94c24ed9a039",
    "classes": [{ "classId": "2590b596-e894-4af5-8ac5-68d109eee995" }]
  },
  {
    "yearId": "bd5b69ac-6638-4d3e-8a52-94c24ed9a039",
    "classes": [{ "classId": "fe4a11f2-5f38-4f7a-bbce-609bc7ad8f99" }]
  }
]

Desired output

[
  {
    "yearId": "bd5b69ac-6638-4d3e-8a52-94c24ed9a039",
    "classes": [
      { "classId": "2590b596-e894-4af5-8ac5-68d109eee995" },
      { "classId": "fe4a11f2-5f38-4f7a-bbce-609bc7ad8f99" }
    ]
  }
]

Is this possible?

Erwin Brandstetter · Accepted Answer · 2022-02-07T21:54:44.890

Hard to say without exact definition of underlying table and and objective of the query.

You need two levels of aggregation in any case. And you can probably largely simplify:

SELECT sub.id, sub."firstName"
     , jsonb_agg(jsonb_build_object('yearId', sub."yearId"
                                  , 'classes', sub.classes)) AS years
FROM  (
   SELECT s.id, s."firstName", cy."yearId"
        , jsonb_agg(jsonb_build_object('classId', cy."classId")) AS classes
   FROM   users s
   LEFT   JOIN "classYearStudents" cys ON cys."studentId" = s.id
   LEFT   JOIN "classYears"        cy  ON cy.id = cys."classYearId"
   GROUP  BY s.id, cy."yearId"
   ) sub
GROUP BY sub.id, sub."firstName";

Not sure if and where you need DISTINCT in this query.

I kept the user ID in the result, as first names are hardly unique.

Don't use CaMeL-case identifiers with Postgres if you can avoid it. See:

Are PostgreSQL column names case-sensitive?

Thank you very much! There were some small things that needed changing but I got it to work! — Maarten Coppens, Feb 07 '22 at 19:22

Nested aggregation with `jsonb_agg`

SQL Output

The problem

Current JSON output

Desired output

1 Answers1