CODEWITHSUNDEEP
sqlpostgresqlaggregate-functions
Maarten Coppens
Maarten Coppens

Reputation: 199

Nested aggregation with `jsonb_agg`

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?

Upvotes: 2

Views: 664

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656804

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:

Upvotes: 2

Related Questions