如何在SQL中对JSON数组字段进行展开并重新分组聚合？-SQL-PHP中文网

postgresql用jsonb_array_elements()展开json数组，需显式转jsonb类型并过滤空数组/null；mysql用json_table()实现等价功能，须确保字段为json类型且声明列结构；展开后须按原始主键group by才能正确聚合。

如何在sql中对json数组字段进行展开并重新分组聚合？

PostgreSQL 中用 `jsonb_array_elements()` 展开 JSON 数组

PostgreSQL 的 jsonb_array_elements() 是最直接的展开方式，它把每个数组元素转成一行。注意它只接受 jsonb 类型，如果字段是 json，得先用 ::jsonb 显式转换。

常见错误是直接对空数组或 NULL 字段调用该函数——这会导致整行丢失（因为 LATERAL 关联时无匹配结果）。必须提前过滤或用 COALESCE 补默认值。

示例：假设表 orders 有字段 items jsonb，内容为 [{"id":1,"qty":2},{"id":2,"qty":1}]：

SELECT o.id, elem->>'id' AS item_id, (elem->>'qty')::int AS qty
FROM orders o,
LATERAL jsonb_array_elements(o.items) AS elem
WHERE o.items != '[]' AND o.items IS NOT NULL;

MySQL 8.0+ 用 `JSON_TABLE()` 实现等价展开

MySQL 没有类似 jsonb_array_elements() 的标量函数，JSON_TABLE() 是唯一可靠方案，但它语法更重、必须声明列名和类型，且不支持嵌套路径动态提取。

容易踩的坑：字段里存的是字符串而非 JSON 值（比如 "[{\"id\":1}]"），JSON_TABLE() 会静默失败或返回空结果；必须确保字段类型是 JSON 或显式用 CAST(col AS JSON) 转换。

示例（同上数据结构）：

SELECT o.id, jt.item_id, jt.qty
FROM orders o,
JSON_TABLE(
  o.items,
  '$[*]' COLUMNS (
    item_id VARCHAR(32) PATH '$.id',
    qty INT PATH '$.qty'
  )
) AS jt
WHERE JSON_VALID(o.items) AND JSON_LENGTH(o.items) > 0;

展开后按原始记录分组再聚合：别漏掉外层 `GROUP BY`

展开后数据行数变多，如果想统计每个订单的总数量、去重商品数等，必须在外层再套一层查询，并按原始主键（如 orders.id）分组。否则聚合会跨订单混算。

网易叭哥说

网易叭哥说是网易有道推出的AI原生语音输入Agent。

下载

典型错误是把聚合写在 LATERAL 或 JSON_TABLE 内部——它们只负责展开，不参与分组逻辑。

正确做法：

先展开成宽表（含原始 ID + 每个数组元素字段）
再用 GROUP BY 原始 ID，配合 SUM()、COUNT(DISTINCT) 等聚合函数
若需保留原始 JSON 字段其他属性（如 order_time），记得在 GROUP BY 中一并列出，或用 ANY_VALUE()（MySQL）/MIN()（PG）包裹

例如统计每单总件数：

-- PostgreSQL
SELECT o.id, ANY_VALUE(o.order_time), SUM((elem->>'qty')::int) AS total_qty
FROM orders o,
LATERAL jsonb_array_elements(o.items) AS elem
GROUP BY o.id;