Oracle SQL实现重复ID数据按ID分组生成JSON格式结果

现有一张包含重复ID的表，需要编写SELECT语句，将每个重复ID对应的所有数据转换为JSON格式输出，且ID不包含在JSON内容中。

原始表数据 #

id,yyyy,name 
1,2010,a  
1,2011,b
2,2010,a
3,2010,c
3,2011,a
4,2011,v

期望输出结果 #

id, column(json) 
1     {"2010":"a","2011":"b"}
2     {"2010":"a"}
3     {"2010":"c","2011":"a"}
4     {"2011":"v"}

注：原期望结果中id=2的JSON存在笔误，对应原始表数据仅一条记录，修正后应为{"2010":"a"}

针对不同数据库的实现方案 #

MySQL 5.7+

使用JSON_OBJECTAGG函数直接聚合键值对：

SELECT 
    id,
    JSON_OBJECTAGG(yyyy, name) AS json_column
FROM 
    your_table -- 替换为你的表名
GROUP BY 
    id;

如果同一ID下存在重复的yyyy值，该函数会保留分组内最后一条匹配的记录值。

PostgreSQL 9.4+

使用json_object_agg函数完成聚合：

SELECT 
    id,
    json_object_agg(yyyy, name) AS json_column
FROM 
    your_table -- 替换为你的表名
GROUP BY 
    id;

若同一ID下存在重复的yyyy键，默认会抛出错误。如果需要保留最后一条记录，可改用jsonb_object_agg并指定排序优先级：

SELECT 
    id,
    jsonb_object_agg(yyyy, name ORDER BY id) AS json_column
FROM 
    your_table -- 替换为你的表名
GROUP BY 
    id;

SQL Server 2016+

通过STRING_AGG拼接键值对字符串，再转换为JSON对象：

SELECT 
    id,
    JSON_QUERY('{' + STRING_AGG('"' + CAST(yyyy AS VARCHAR(4)) + '":"' + REPLACE(name, '"', '""') + '"', ',') + '}') AS json_column
FROM 
    your_table -- 替换为你的表名
GROUP BY 
    id;

使用REPLACE是为了转义name字段中的双引号，避免生成非法JSON格式。

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