JSON_EXTRACT直接SUM失败因返回带引号字符串或JSON类型值,须用JSON_UNQUOTE或CAST转数值;数组求和需JSON_TABLE展开,旧版可用索引模拟但性能差;健壮处理需JSON_TYPE分支判断并兜底。

JSON_EXTRACT 提取后直接 SUM 会失败的典型表现
执行 SUM(JSON_EXTRACT(data, '$.amount')) 却返回 NULL 或 0,不是数据为空,而是提取结果带双引号(如 "123.45")——MySQL 不会自动把 JSON 字符串转成数字参与计算。
- 必须用
JSON_UNQUOTE()去掉外层引号,否则SUM当作字符串处理,隐式转 0 - 若字段本身是数字类型(非字符串),
JSON_EXTRACT返回的是 JSON 类型值,仍需强制转为数值类型,推荐用CAST(... AS DECIMAL)或+ 0 - 遇到空值或非法 JSON,
JSON_EXTRACT返回NULL,SUM会跳过,但容易误判为“没数据”
对 JSON 数组内每个元素的 amount 字段求和(标准写法)
假设表 orders 的 items 是 JSON 数组:[{"id":1,"amount":100},{"id":2,"amount":200}],要算所有 amount 总和:
SELECT SUM(CAST(jt.amount AS DECIMAL(10,2))) AS total
FROM orders,
JSON_TABLE(items, '$[*]' COLUMNS (
amount VARCHAR(50) PATH '$.amount'
)) AS jt;
-
JSON_TABLE是 MySQL 8.0+ 必选项,它把数组“展开”成行集,比手写CROSS JOIN+ 索引更可靠 -
PATH '$.amount'提取字段值,即使原 JSON 中amount是数字或字符串,都先取为VARCHAR,再由CAST统一转类型 - 别用
JSON_EXTRACT(items, '$[*].amount')——它返回的是 JSON 数组(如[100, 200]),不能被SUM直接消费
兼容旧版本(MySQL 5.7)的降级方案
没有 JSON_TABLE 时,只能靠手动展开索引。但必须预估最大数组长度,否则漏项:
一个基于官方 Mailtrap Node.js SDK 构建的 ASE 技能,用于发送事务邮件并与 Mailtrap 沙盒或生产流程协作。它非常适合需要 API 驱动邮件投递、测试邮箱验证和结构化消息自动化的代理工作流。
SELECT SUM(
CAST(
COALESCE(
JSON_EXTRACT(items, CONCAT('$.[', idx.n, '].amount')),
'0'
) AS DECIMAL(10,2))
) AS total
FROM orders
CROSS JOIN (
SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
) AS idx
WHERE JSON_LENGTH(items) > idx.n;
-
JSON_LENGTH(items) > idx.n是关键过滤条件,避免越界提取出NULL -
COALESCE(..., '0')防止某条目缺失amount导致整行变NULL,拖垮SUM - 这个方案性能差、难维护,仅建议临时救急;长期请升级到 8.0+ 并改用
JSON_TABLE
遇到字符串型数字(如 "123.45")时的健壮处理
很多业务 JSON 里存的是字符串数字(比如前端传参未 parse),光靠 CAST 可能报错。安全做法是加类型校验:
SELECT SUM(
CASE
WHEN JSON_TYPE(JSON_EXTRACT(items, '$.amount')) = 'STRING'
THEN CAST(JSON_UNQUOTE(JSON_EXTRACT(items, '$.amount')) AS DECIMAL(10,2))
WHEN JSON_TYPE(JSON_EXTRACT(items, '$.amount')) = 'NUMBER'
THEN CAST(JSON_EXTRACT(items, '$.amount') AS DECIMAL(10,2))
ELSE 0
END
) AS total
FROM orders;
- 用
JSON_TYPE()显式判断原始类型,比依赖隐式转换更可控 -
JSON_UNQUOTE只对字符串型 JSON 值有效;对数字型调用会返回NULL,所以必须分支处理 - 线上环境务必加
ELSE 0,避免单条脏数据让整个聚合中断
真正麻烦的不是语法,而是 JSON 字段里藏了多少种“看似一样、实则类型不同”的数字表达方式——一次 CAST 解决不了的,得靠 JSON_TYPE + 分支 + 默认兜底。










