MySQL 如何用 JSON_TABLE 展开 JSON 数组并聚合统计-SQL

JSON_TABLE 用于将 JSON 数组“炸开”为关系型行集，必须配合 LATERAL 在 FROM 子句中使用；其核心能力是结构化展开嵌套数组，支持多字段提取、JOIN、GROUP BY 和窗口函数，不可被 JSON_CONTAINS 或 JSON_EXTRACT 替代。

JSON_TABLE 怎么把 JSON 数组转成行？

JSON_TABLE 的核心作用是把 JSON 文档里的数组“炸开”成关系型行集，不是解析单个值。它必须配合 LATERAL（或隐式 LATERAL）在 FROM 子句中使用，不能直接用于 SELECT 列表里。

常见错误是写成 SELECT JSON_TABLE(...) —— 这会报错：ERROR 1064 (42000): You have an error in your SQL syntax。

正确姿势是：

SELECT jt.tag, COUNT(*) AS cnt
FROM articles,
JSON_TABLE(
  content_json,
  '$.tags[*]' COLUMNS (tag TEXT PATH '$')
) AS jt
WHERE jt.tag IS NOT NULL;

注意三点：

• content_json 是表中存 JSON 的列名，类型应为 JSON 或能自动转换的字符串（如 TEXT）
• 路径 '$.tags[]' 中的 [] 表示遍历数组每个元素；如果路径没匹配到数组（比如字段为空、null 或非数组），JSON_TABLE 返回空结果集，整行被丢弃
• COLUMNS 里用 PATH '$' 表示取当前数组元素本身；若元素是对象（如 {"name": "mysql", "level": 2}），可写 name TEXT PATH '$.name'

聚合统计时为什么 COUNT(*) 结果比预期少？

根本原因是 JSON_TABLE 对 null、非法 JSON、非数组值静默跳过——不报错，也不生成行。

典型场景：

• content_json 列含 NULL 值 → 该行完全不出现在结果中
• $.tags 字段存在但值为字符串 "backend"（不是数组）→ 该行无 jt 行产出
• JSON 格式错误（如多逗号、单引号）→ 整个 JSON_TABLE 表达式返回空

解决办法是先过滤再展开：

SELECT jt.tag, COUNT(*) AS cnt
FROM articles
WHERE JSON_VALID(content_json)
  AND JSON_TYPE(JSON_EXTRACT(content_json, '$.tags')) = 'ARRAY'
  AND JSON_LENGTH(JSON_EXTRACT(content_json, '$.tags')) > 0,
JSON_TABLE(
  content_json,
  '$.tags[*]' COLUMNS (tag TEXT PATH '$')
) AS jt;

更稳妥的做法是用 COALESCE + JSON_EXTRACT 预处理默认空数组：

JSON_TABLE(
  COALESCE(
    JSON_EXTRACT(content_json, '$.tags'),
    '[]'
  ),
  '$[*]' COLUMNS (tag TEXT PATH '$')
) AS jt

性能差得离谱？检查这几个地方

JSON_TABLE 是逐行执行的，无法利用索引加速展开过程。当表有 10 万行、平均每行展开 5 个 tag，实际扫描行数就是 50 万 —— 这还没算 JSON 解析开销。