MySQL中JSON数组需用JSON_TABLE展开后统计,COUNT()等聚合函数无法直接作用于JSON数组字段。

JSON数组字段在MySQL中无法直接用COUNT()统计内部元素
MySQL 5.7+ 支持 JSON 类型,但聚合函数如 COUNT()、SUM() 不能直接作用于 JSON 数组本身——它们会把整个 JSON 值当作单个标量处理,返回 1 或 NULL。真正要统计的是数组里每个对象的某个属性(比如 status 出现次数),必须先展开数组。
关键路径是:用 JSON_TABLE() 将 JSON 数组转为关系行集 → 再对生成的列做聚合。
常见错误现象:COUNT(json_column->'$.items[*].status') 返回 1,不是数组长度;SUM(json_column->>'$.items[*].price') 报错或返回 0。
-
JSON_TABLE()是唯一标准方式(MySQL 8.0.22+),替代已废弃的JSON_EXTRACT()+JSON_LENGTH()手动模拟 - 必须显式声明列名和类型,否则生成的临时表列不可用于
GROUP BY - 若 JSON 字段为 NULL 或格式非法,整行会被跳过(不报错),需提前用
JSON_VALID()过滤
用JSON_TABLE展开数组并按status分组计数
假设表 orders 有字段 details JSON,内容如:{"items": [{"name":"A","status":"shipped"},{"name":"B","status":"pending"}]},目标是统计所有订单中各 status 出现总次数。
SELECT jt.status, COUNT(*) AS cnt
FROM orders
CROSS JOIN JSON_TABLE(
details, '$.items[*]'
COLUMNS (
status VARCHAR(20) PATH '$.status'
)
) AS jt
WHERE JSON_VALID(details)
GROUP BY jt.status;
说明:
一个基于官方 Mailtrap Node.js SDK 构建的 ASE 技能,用于发送事务邮件并与 Mailtrap 沙盒或生产流程协作。它非常适合需要 API 驱动邮件投递、测试邮箱验证和结构化消息自动化的代理工作流。
-
'$.items[*]'是 JSON 路径,表示遍历items数组每个元素 -
COLUMNS (status VARCHAR(20) PATH '$.status')定义输出列,类型必须匹配实际值,否则截断或转空字符串 -
CROSS JOIN是必须的,不能写成LEFT JOIN(否则空数组会补 NULL 行,干扰计数) - 如果只要某类 status(如只统计
"shipped"),加HAVING jt.status = 'shipped'或在WHERE里过滤
PostgreSQL中用jsonb_array_elements_text()更简洁
PostgreSQL 的 jsonb 类型原生支持数组展开,无需定义 schema。若字段为 details JSONB,且想统计 tags 数组中各标签频次:
SELECT elem::TEXT AS tag, COUNT(*) AS cnt FROM orders, LATERAL jsonb_array_elements_text(details->'tags') AS elem WHERE details ? 'tags' GROUP BY elem;
说明:
-
LATERAL允许右侧子查询引用左侧表字段,jsonb_array_elements_text()直接返回文本元素(自动转义) -
details ? 'tags'比details->'tags' IS NOT NULL更可靠,能准确判断 key 是否存在 - 若数组内是对象(非纯文本),改用
jsonb_array_elements(),再用->>'status'提取字段 - 注意性能:大表上未建 GIN 索引时,
jsonb_array_elements可能全表扫描,建议对高频查询路径预计算物化列
SQLite中只能靠JSON1扩展+递归CTE硬解
SQLite 3.38+ 自带 JSON1 扩展,但无内置数组展开函数。要统计 data TEXT 字段中 logs 数组的 level 分布,得用递归 CTE 模拟循环:
WITH RECURSIVE json_items(idx, item, rest) AS (
SELECT 0,
json_extract(data, '$.logs[0]'),
json_remove(data, '$.logs[0]')
FROM events
WHERE json_type(data, '$.logs') = 'array' AND json_array_length(data, '$.logs') > 0
UNION ALL
SELECT idx + 1,
json_extract(rest, '$.logs[0]'),
json_remove(rest, '$.logs[0]')
FROM json_items
WHERE json_array_length(rest, '$.logs') > 0
)
SELECT json_extract(item, '$.level') AS level, COUNT(*) AS cnt
FROM json_items
GROUP BY level;
说明:
- 每次递归只取数组首项(
'$.logs[0]'),再用json_remove删掉它,直到数组为空 - 必须检查
json_type()和json_array_length(),否则遇到非数组或空数组会无限递归或崩溃 - 性能极差:N 行 × M 元素 = O(N×M) 次 JSON 解析,仅适用于小数据集或离线分析
- 生产环境强烈建议在应用层解析,或迁移到支持
JSON_TABLE的数据库
最易被忽略的是 JSON 路径语法差异:MySQL 用 $[*],PostgreSQL 用 -> 和 ->>,SQLite 用 json_extract() 函数。写错一个符号,结果就全空。另外,所有数据库对非法 JSON 都静默失败,务必加校验逻辑。

















