Dev.to

Summarize Row Counts by Dynamic Columns into JSON — From SQL to SPL #22

Problem description & analysis: Each column of database table tempdata can be viewed as an array, with duplicate members: Task: Now we need to group and count each column separately, in JSON format: Code comparisons: SQL: With Grouped AS ( SELECT td.hobbies_1, td.hobbies_2, COUNT(*) AS count FROM _temp_data td GROUP BY GROUPING SETS ( (td.hobbies_1), (td.hobbies_2) ) ) SELECT jsonb_object_agg(g.hobbies_1, g.count) FILTER (WHERE g.hobbies_1 IS NOT NULL) AS hobbies_1, jsonb_object_agg(g.hobbies_2, g.count) FILTER (WHERE g.hobbies_2 IS NOT NULL) AS hobbies_2 FROM Grouped g; First uses GROUPING SETS to group and count different columns simultaneously, and then uses jsonb_object_agg to convert each record set into JSON separately. The code is quite cumbersome; And the column names must be written, which is less flexible; If you want to support dynamic column names, you need to use stored procedures, and the structure will become complex. SPL: SPL does not need to write column names:

Apr 24, 2025 - 03:56

Summarize Row Counts by Dynamic Columns into JSON — From SQL to SPL #22

Problem description & analysis:

Each column of database table tempdata can be viewed as an array, with duplicate members:

Task: Now we need to group and count each column separately, in JSON format:

Code comparisons:

SQL:

With Grouped AS (
    SELECT
      td.hobbies_1,
      td.hobbies_2,
      COUNT(*) AS count
    FROM _temp_data td
    GROUP BY GROUPING SETS (
      (td.hobbies_1),
      (td.hobbies_2)
    )
)
SELECT
  jsonb_object_agg(g.hobbies_1, g.count) FILTER (WHERE g.hobbies_1 IS NOT NULL) AS hobbies_1,
  jsonb_object_agg(g.hobbies_2, g.count) FILTER (WHERE g.hobbies_2 IS NOT NULL) AS hobbies_2
FROM Grouped g;

First uses GROUPING SETS to group and count different columns simultaneously, and then uses jsonb_object_agg to convert each record set into JSON separately. The code is quite cumbersome; And the column names must be written, which is less flexible; If you want to support dynamic column names, you need to use stored procedures, and the structure will become complex.

SPL: SPL does not need to write column names: