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

postgresql中用jsonb_array_elements()展开jsonb数组必须配合lateral，显式写为cross join lateral jsonb_array_elements(coalesce(data, '[]'::jsonb)) as elem，避免null或空数组导致整行丢失；字段须为jsonb类型，对象元素取值用->>转文本。

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

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

PostgreSQL 是目前最常用且稳定支持 JSON 数组展开的场景。如果你的字段是 jsonb 类型（推荐），直接用 jsonb_array_elements() 就能一行变多行。注意它只接受 jsonb，传 json 会报错：function jsonb_array_elements(json) does not exist。

常见错误是字段明明存的是数组，但查出来是 null —— 很可能因为原始数据是 json 类型且含多余空格或换行，导致解析失败；或者字段本身是 null 或不是数组（比如是对象或字符串），这时 jsonb_array_elements() 返回空结果集，不是报错，容易被忽略。

确保字段类型为 jsonb：用 ALTER TABLE t ALTER COLUMN data TYPE jsonb USING data::jsonb;
安全展开：加 CROSS JOIN LATERAL jsonb_array_elements(COALESCE(data, '[]'::jsonb)) AS elem，避免因 null 导致整行丢失
如果数组元素是对象，想取其中某个字段（如 elem->>'tag'），记得用 ->>（转字符串）或 ->（保持 JSONB）

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

MySQL 不支持类似 PostgreSQL 的 lateral 展开，必须用 JSON_TABLE() —— 它本质是把 JSON 数组“映射”成一张临时表。难点在于路径表达式写法和列定义必须严格匹配，否则返回空或报错：Invalid JSON path expression。

典型陷阱是数组索引从 $[0] 开始，但很多人误写成 $[*]（MySQL 不支持通配符索引语法）。另外，如果原 JSON 字段是 NULL 或非数组，JSON_TABLE() 默认跳过该行，不像 PostgreSQL 那样可配合 COALESCE 控制。

抖音下载器(Node.js)

抖音无水印视频下载和文案提取工具

下载

基础写法：JSON_TABLE(data, '$[*]' COLUMNS (tag VARCHAR(50) PATH '$.tag')) AS jt
处理可能为 NULL 的字段：先用 IFNULL(data, '[]') 包一层再进 JSON_TABLE
COLUMNS 中的 PATH 必须以 $. 开头，不能漏掉根符号；嵌套字段如 $.info.name 要确保路径存在，否则该列得设 EXISTS 或用 ON ERROR NULL

SQL Server 中用 `OPENJSON()` 解析并关联分组

SQL Server 的 OPENJSON() 是最接近“函数式展开”的方案，但它必须配合 WITH 子句声明结构，否则只返回键值对（key, value, type）。如果数组里是纯字符串或数字，不声明 WITH 就只能拿到 value 字段，类型还是 nvarchar，后续聚合要手动 CAST。

另一个坑是：OPENJSON() 默认只解析顶层 JSON，如果字段本身是 JSON 对象（如 {"tags": ["a","b"]}），得先用 $.tags 提取数组，再套一层 OPENJSON() —— 嵌套调用容易漏掉 AS 别名或搞混作用域。

展开数组字段：CROSS APPLY OPENJSON(data) WITH (tag NVARCHAR(50) '$')（假设数组元素是字符串）
展开嵌套数组：CROSS APPLY OPENJSON(data, '$.items') WITH (name NVARCHAR(100) '$.name')
聚合前注意类型：字符串数字（如 "123"）需 TRY_CAST(tag AS INT)，否则 SUM() 会静默失败或返回 0

分组聚合时别忽略 JSON 元素的去重与空值语义

展开后直接 GROUP BY 很容易得到错误结果，因为同一个原始记录可能产生多条相同标签（比如数组重复存了 ["a","a"]），而业务上是否要去重，取决于场景。另外，NULL 在 JSON 中可能是 null 字面量，也可能是缺失字段，在不同数据库中展开行为不一致。

去重计数：用 COUNT(DISTINCT elem->>'tag')（PG）或 COUNT(DISTINCT jt.tag)（MySQL），而不是 COUNT(*)
区分空和缺失：PostgreSQL 中 elem->>'tag' 对缺失字段返回 NULL，而 elem->'tag' 返回 NULL（JSONB null）；MySQL 中 PATH '$.tag' 对缺失返回 NULL，但无法区分是 null 还是不存在
聚合前建议先 WHERE tag IS NOT NULL AND TRIM(tag) != ''，尤其当源数据质量不可控时