SQL实现按订单分组将所有商品项聚合为单行JSON数组

需求说明 #

• 源表（订单明细表/表1）：每行存储单个订单关联的单个商品，核心字段为OrderID（订单ID）、ItemId（商品ID）
• 目标结果（订单聚合表/表2）：按OrderID聚合为单行，关联商品组装为JSON数组存入Items字段，数组内每个元素结构为{"ItemId": 对应商品ID}，样例输出：

  OrderID	Items
  1	[{"ItemId":1},{"ItemId":2}]
  2	[{"ItemId":3}]

不同数据库的实现SQL #

不同数据库的JSON处理函数存在差异，根据实际使用的数据库选择对应写法即可，以下示例默认源表名为order_detail，实际使用时替换为真实表名即可。

MySQL 5.7及以上版本 #

使用原生JSON聚合函数，输出为标准JSON类型，不会出现特殊字符转义问题：

SELECT
  OrderID,
  JSON_ARRAYAGG(JSON_OBJECT('ItemId', ItemId)) AS Items
FROM order_detail
GROUP BY OrderID;

PostgreSQL #

支持JSON/JSONB两种类型，追求更高查询性能可以选择JSONB写法：

-- 输出JSON类型
SELECT
  OrderID,
  json_agg(json_build_object('ItemId', ItemId)) AS Items
FROM order_detail
GROUP BY OrderID;

-- 输出JSONB类型（推荐）
SELECT
  OrderID,
  jsonb_agg(jsonb_build_object('ItemId', ItemId)) AS Items
FROM order_detail
GROUP BY OrderID;

SQL Server 2016及以上版本 #

通过FOR JSON PATH语法直接生成符合要求的JSON数组：

SELECT
  t1.OrderID,
  Items = (
    SELECT ItemId
    FROM order_detail t2
    WHERE t2.OrderID = t1.OrderID
    FOR JSON PATH
  )
FROM order_detail t1
GROUP BY t1.OrderID;

Hive/Spark SQL（大数据场景） #

通过结构体聚合后转JSON字符串实现：

SELECT
  OrderID,
  to_json(collect_list(named_struct('ItemId', ItemId))) AS Items
FROM order_detail
GROUP BY OrderID;

低版本无原生JSON函数的兼容写法 #

如果数据库版本不支持上述原生JSON函数，可以通过字符串拼接实现（注意：如果ItemId为字符串类型、值包含引号等特殊字符，需要额外增加转义逻辑，优先使用原生JSON函数方案）：

-- MySQL低版本示例
SELECT
  OrderID,
  CONCAT('[', GROUP_CONCAT(CONCAT('{"ItemId":', ItemId, '}') SEPARATOR ','), ']') AS Items
FROM order_detail
GROUP BY OrderID;

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