在MySQL 8.0及以上版本中，JSON_TABLE函数可以将JSON数据转换为关系型表结构，结合子查询和嵌套使用的方式，能够高效处理多层嵌套的复杂JSON数据，满足各类业务场景的数据提取需求。
JSON_TABLE基本用法回顾
JSON_TABLE的作用是将JSON数组或对象展开成多行多列的关系型数据，基本语法结构如下：
-- 基础JSON_TABLE语法示例
SELECT *
FROM JSON_TABLE(
'[{"id": 1, "name": "张三"}, {"id": 2, "name": "李四"}]', -- JSON数据源
"$[*]" COLUMNS ( -- 路径表达式，[*]表示遍历数组所有元素
id INT PATH "$.id", -- 提取id字段，映射为INT类型列
name VARCHAR(20) PATH "$.name" -- 提取name字段，映射为VARCHAR类型列
)
) AS jt;
上述语句会将传入的JSON数组转换为两行两列的关系表，每一行对应一个JSON对象，列对应提取的字段。
子查询结合JSON_TABLE实现嵌套提取
当JSON数据存储在表的字段中，且结构存在多层嵌套时，可以先通过子查询获取目标JSON数据，再嵌套使用JSON_TABLE提取内容。假设存在用户表user_info，其中ext_data字段存储用户的扩展JSON信息，结构如下：
-- 创建测试表并插入数据
CREATE TABLE user_info (
user_id INT PRIMARY KEY,
ext_data JSON
);
INSERT INTO user_info VALUES
(1, '{"hobbies": [{"type": "运动", "items": ["跑步", "游泳"]}, {"type": "阅读", "items": ["小说", "散文"]}], "address": "北京"}'),
(2, '{"hobbies": [{"type": "音乐", "items": ["吉他", "钢琴"]}], "address": "上海"}');
第一步：子查询提取原始JSON数据
首先通过子查询筛选出需要的用户JSON数据，避免全表扫描带来的性能损耗：
-- 子查询获取目标用户的ext_data SELECT ext_data FROM user_info WHERE user_id = 1;
第二步：嵌套JSON_TABLE提取多层数据
上述子查询得到的ext_data中，hobbies是数组，每个数组元素下的items也是数组，需要嵌套使用JSON_TABLE逐层提取：
-- 子查询结合嵌套JSON_TABLE提取所有爱好明细
SELECT
jt1.type AS hobby_type, -- 第一层提取的爱好类型
jt2.item AS hobby_item -- 第二层提取的爱好具体项
FROM (
-- 子查询：获取用户1的扩展数据
SELECT ext_data
FROM user_info
WHERE user_id = 1
) AS sub
-- 第一层JSON_TABLE：提取hobbies数组的每个元素
CROSS JOIN JSON_TABLE(
sub.ext_data,
"$.hobbies[*]" COLUMNS (
type VARCHAR(10) PATH "$.type",
items_json JSON PATH "$.items" -- 保留items数组用于第二层提取
)
) AS jt1
-- 第二层JSON_TABLE：提取items数组的每个元素
CROSS JOIN JSON_TABLE(
jt1.items_json,
"$[*]" COLUMNS (
item VARCHAR(10) PATH "$" -- 提取数组中的每个字符串元素
)
) AS jt2;
执行上述语句后，会得到如下结果：
| hobby_type | hobby_item |
|---|---|
| 运动 | 跑步 |
| 运动 | 游泳 |
| 阅读 | 小说 |
| 阅读 | 散文 |
复杂场景下的优化建议
- 子查询中尽量添加合理的WHERE条件，减少参与JSON_TABLE转换的数据量，提升查询性能
- 嵌套JSON_TABLE的层数不宜过多，超过3层时建议先通过临时表存储中间结果，再进行后续提取
- 对频繁进行JSON提取的字段，可以考虑增加虚拟列并创建索引，降低JSON解析的开销
常见问题说明
如果JSON路径不存在对应字段，JSON_TABLE会返回NULL值，若需要过滤空值，可以在外层查询添加WHERE条件过滤
当JSON数据结构不固定时，可以先通过JSON_TYPE()函数判断字段类型，再动态调整JSON_TABLE的路径表达式，避免查询报错。
MySQLJSON_TABLE子查询JSON数据提取修改时间：2026-07-03 19:48:23