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

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 解析开销。

关键瓶颈点：

• 没对原始 JSON 列建函数索引（MySQL 8.0.13+ 支持）：ALTER TABLE articles ADD COLUMN tags_array JSON AS (JSON_EXTRACT(content_json, '$.tags')) STOred;，然后在 tags_array 上建虚拟列索引
• 在 WHERE 条件里对 JSON_EXTRACT 做过滤（如 JSON_CONTAINS(..., '"mysql"')），会导致全表扫描 JSON 列
• 展开后未及时 GROUP BY，让中间结果集膨胀（例如展开 100 万行才聚合）

建议操作顺序：

• 先用 JSON_LENGTH 粗筛： WHERE JSON_LENGTH(JSON_EXTRACT(content_json, '$.tags')) > 0
• 展开后立刻 GROUP BY jt.tag，避免大中间集
• 若高频查某几个 tag，考虑冗余字段（如 is_mysql_tag TINYINT）并建索引

和 JSON_CONTAINS、JSON_EXTRACT 比有什么不可替代性？

JSON_CONTAINS 只能回答“有没有”，JSON_EXTRACT 只能取固定路径的值；只有 JSON_TABLE 能真正实现「数组扁平化 + 多字段提取 + 关联聚合」。

比如 JSON 是：{"user": "alice", "actions": [{"type": "login", "ts": 1710000000}, {"type": "post", "ts": 1710000100}]}

你想统计每个用户的 login 次数、平均间隔：

SELECT    u.name,   COUNT(*) FILTER (WHERE a.type = 'login') AS login_cnt,   AVG(a.ts - LAG(a.ts) OVER (PARTITION BY u.name ORDER BY a.ts)) FROM logs, JSON_TABLE(log_data, '$' COLUMNS (   name TEXT PATH '$.user',   NESTED PATH '$.actions[*]' COLUMNS (     type TEXT PATH '$.type',     ts BIGINT PATH '$.ts'   ) )) AS u, LATERAL (SELECT * FROM JSON_TABLE(log_data, '$.actions[*]' COLUMNS (   type TEXT PATH '$.type',   ts BIGINT PATH '$.ts' )) AS a) AS a GROUP BY u.name;

嵌套 NESTED PATH 和 LATERAL 关联才是它不可替代的地方——其他 JSON 函数做不到这种结构化展开。

实际用的时候，别指望它快，但要清楚它唯一能干成的事：把深嵌套、变长数组变成可 JOIN、可 GROUP BY、可 winDOW 的真实行。一旦需要跨数组元素做计算，绕不开它。