按ID/Section组合将多行数据合并为带JSON列的单行

现有如下结构的数据库表：

需要通过SQL查询将其转换为以下格式：按ID/Section组合合并成单行，把对应国家与日期整合为嵌套JSON字符串：

实现方案 #

MySQL 版本 #

通过两次分组聚合结合JSON函数实现：

1. 子查询先按ID、Section、Date分组，将同一日期下的国家用分号拼接：

SELECT 
  ID,
  Section,
  Date,
  GROUP_CONCAT(Country SEPARATOR ';') AS country_group
FROM your_table
GROUP BY ID, Section, Date;

2. 外层查询按ID、Section分组，用JSON_OBJECTAGG生成目标JSON结构：

SELECT 
  CONCAT('"', ID, ',', Section, '"') AS `ID/Section`,
  JSON_OBJECTAGG(country_group, CONCAT(Date, ';')) AS `Country/dates`
FROM (
  SELECT 
    ID,
    Section,
    Date,
    GROUP_CONCAT(Country SEPARATOR ';') AS country_group
  FROM your_table
  GROUP BY ID, Section, Date
) AS temp_result
GROUP BY ID, Section;

PostgreSQL 版本 #

使用string_agg拼接字符串，json_object_agg生成JSON：

SELECT 
  CONCAT('"', id, ',', section, '"') AS "ID/Section",
  json_object_agg(country_group, CONCAT(date, ';')) AS "Country/dates"
FROM (
  SELECT 
    id,
    section,
    date,
    string_agg(country, ';') AS country_group
  FROM your_table
  GROUP BY id, section, date
) AS temp_result
GROUP BY id, section;

注意：需将your_table替换为实际表名，不同数据库的JSON函数语法可能有细微差异，需根据使用的数据库调整。

内容的提问来源于stack exchange，提问作者Han Brolo