在业务数据库里，经常有人把本来该拆成子表的多值数据，直接塞进一个JSON类型的字段中，最常见的就是JSON数组。比如一张用户表有个tags字段，里面存了["php","go","sql"]这样的标签数组。如果现在想统计每个标签被多少人使用，就要先把数组拆开，再聚合。用JSON_TABLE就能在SQL里完成这件事。
什么是JSON_TABLE
JSON_TABLE是SQL标准里用来把JSON文本转成虚拟关系表的函数。它会按照你给的路径，把JSON里的数组或对象展开成多行多列，之后就能像普通表一样做查询和聚合。
示例表结构
假设我们有如下用户表，字段profile是JSON类型，里面的skills是字符串数组：
CREATE TABLE user_profile (
id INT PRIMARY KEY,
name VARCHAR(50),
profile JSON
);
INSERT INTO user_profile VALUES
(1, '张三', '{"skills":["sql","go","redis"]}'),
(2, '李四', '{"skills":["sql","php"]}'),
(3, '王五', '{"skills":["go","python"]}');
用JSON_TABLE展开数组并聚合
下面的SQL把profile中的skills数组展开，统计每种技能的出现人数：
SELECT
j.skill AS skill_name,
COUNT(*) AS user_count
FROM
user_profile,
JSON_TABLE(
profile,
'$.skills[*]' COLUMNS (
skill VARCHAR(50) PATH '$'
)
) AS j
GROUP BY
j.skill
ORDER BY
user_count DESC;
这里JSON_TABLE的第二个参数'$.skills[*]'表示取skills数组的每一个元素，COLUMNS里定义展开后的列skill，PATH '$'表示取当前数组元素的值。主查询把原表和这个虚拟表做隐式JOIN，于是每个用户行会变成多行，再GROUP BY就能聚合。
结果示例
| skill_name | user_count |
|---|---|
| sql | 2 |
| go | 2 |
| redis | 1 |
| php | 1 |
| python | 1 |
统计数组元素个数
如果只想看每个用户有几个skill，可以用JSON_LENGTH配合普通查询：
SELECT name, JSON_LENGTH(profile, '$.skills') AS skill_num FROM user_profile;
去重聚合与求和场景
当JSON数组里是数字，比如每月消费明细，也可以用JSON_TABLE展开后SUM：
SELECT
u.name,
SUM(j.amount) AS total_cost
FROM
user_profile u,
JSON_TABLE(
u.profile,
'$.costs[*]' COLUMNS (
amount DECIMAL(10,2) PATH '$'
)
) AS j
GROUP BY
u.name;
注意事项
- MySQL 8.0+、Oracle 12c+才原生支持JSON_TABLE，低版本需要自己写存储过程解析。
- JSON路径写错不会报错而是返回空，排查时先用SELECT单独跑JSON_TABLE看行数。
- 大数据量下展开数组会产生很多中间行，建议加索引或先过滤再展开。
通过JSON_TABLE把JSON字段里的数组展开成行，再结合COUNT、SUM、GROUP BY，就能在数据库内一站式完成以前要写代码才能做的统计，既清晰又高效。
SQLJSON_TABLE数组展开聚合修改时间：2026-07-25 15:12:22