mysql如何处理JSON字段中的嵌套数据？JSON_EXTRACT与路径表达式怎么用

mysql从5.7版本开始原生支持JSON数据类型，允许在表中直接存储结构化的JSON数据，当JSON数据存在多层嵌套结构时，可以通过JSON_EXTRACT函数配合路径表达式快速提取目标内容。

JSON字段嵌套数据的存储形式

mysql中的JSON字段可以存储对象、数组以及两者的嵌套组合，常见的嵌套结构示例如下，我们创建一张用户扩展信息表来存储这类数据：

-- 创建测试表
CREATE TABLE user_extend (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    ext_info JSON NOT NULL
);

-- 插入包含嵌套JSON的测试数据
INSERT INTO user_extend (user_id, ext_info) VALUES (
    1,
    '{
        "base": {
            "name": "张三",
            "age": 28,
            "address": {
                "province": "广东",
                "city": "深圳"
            }
        },
        "hobbies": ["篮球", "阅读", "旅行"],
        "scores": [
            {"subject": "数学", "score": 95},
            {"subject": "语文", "score": 88}
        ]
    }'
);

JSON_EXTRACT函数基本用法

JSON_EXTRACT是mysql用于从JSON数据中提取指定内容的函数，语法格式为JSON_EXTRACT(json_doc, path[, path] ...)，第一个参数是JSON字段或者JSON格式的字符串，后面的参数是路径表达式，函数会返回路径匹配到的所有数据。

如果只需要提取单个路径的内容，也可以直接使用->运算符，它的效果等同于JSON_EXTRACT，语法为json_doc -> path，如果不需要返回带引号的字符串，还可以使用->>运算符，会自动去除结果的外层引号。

路径表达式语法规则

路径表达式是定位JSON数据的核心，所有路径都以$开头，代表整个JSON文档，后续通过不同的符号定位不同结构的数据：

对象路径定位

对于JSON对象，使用.key的形式定位对应键的值，比如$.base.name表示定位根对象下base键对应的对象里的name键的值。

数组路径定位

对于JSON数组，使用[index]的形式定位对应索引的元素，索引从0开始，比如$.hobbies[0]表示定位根对象下hobbies数组的第一个元素。

通配符使用

路径表达式中支持两个通配符：*匹配对象的所有键，[]匹配数组的所有元素，比如$.base.*会返回base对象下的所有值，$.hobbies[*]会返回hobbies数组的所有元素。

递归路径匹配

使用**可以递归匹配所有层级的对应键，比如$**.city会匹配整个JSON文档中所有层级的city键的值。

嵌套数据提取实战案例

提取对象嵌套的基础信息

提取用户所在的城市，需要定位到address下的city键：

-- 使用JSON_EXTRACT函数
SELECT JSON_EXTRACT(ext_info, '$.base.address.city') AS city FROM user_extend WHERE user_id = 1;

-- 使用->运算符
SELECT ext_info->'$.base.address.city' AS city FROM user_extend WHERE user_id = 1;

-- 使用->>运算符去除引号
SELECT ext_info->>'$.base.address.city' AS city FROM user_extend WHERE user_id = 1;

提取数组指定位置的元素

提取用户的第一个爱好：

SELECT ext_info->>'$.hobbies[0]' AS first_hobby FROM user_extend WHERE user_id = 1;

提取数组对象中的特定字段

提取所有学科的成绩，需要遍历scores数组，再取每个对象的score键：

SELECT JSON_EXTRACT(ext_info, '$.scores[*].score') AS all_scores FROM user_extend WHERE user_id = 1;

递归匹配所有城市信息

如果JSON结构不确定city所在的层级，可以用递归路径匹配：

SELECT JSON_EXTRACT(ext_info, '$**.city') AS all_city FROM user_extend WHERE user_id = 1;

使用注意事项

• 路径表达式中的键名如果包含特殊字符或者空格，需要用双引号包裹，比如$."user name"。
• 如果路径不存在，JSON_EXTRACT会返回NULL，不会报错，查询时需要注意空值处理。
• 路径表达式里的字符串不需要转义单引号，因为路径本身是用单引号包裹的，内部直接写双引号即可。
• 对JSON字段的查询如果频繁使用，可以考虑给路径表达式创建虚拟列并添加索引，提升查询效率。

mysql处理JSON嵌套数据的核心就是熟练掌握JSON_EXTRACT函数和路径表达式的语法，多结合实际嵌套结构练习就能快速上手。

mysqlJSON_EXTRACT路径表达式JSON字段嵌套数据修改时间：2026-07-17 17:51:24