使用MySQL JSON查询筛选嵌套字段的值方式
自MySQL 5.7版本起，原生支持了JSON数据类型，这为存储和查询半结构化数据提供了极大的便利。然而，随着数据结构的复杂化，如何高效、准确地从嵌套的JSON字段中筛选数据，成为了开发人员经常面临的挑战。本文将详细介绍在MySQL中查询和筛选嵌套JSON字段值的多种实用方式。
一、理解JSON路径表达式
在查询嵌套JSON之前，必须先掌握MySQL的JSON路径表达式语法。所有的JSON函数都依赖路径来定位数据：
$：代表当前JSON文档的根对象。
.：表示对象的成员访问符，例如
$.name。[]：表示数组的索引，从0开始，例如
$.hobbies[0]。.* 或 [*]：表示对象的所有成员或数组的所有元素。
二、基础数据准备
假设我们有一张用户表 users，其中包含一个JSON类型的 info 字段，数据结构如下：
{
"profile": {
"age": 28,
"skills": ["Java", "MySQL", "Python"]
},
"contacts": [
{"type": "email", "value": "user@test.com"},
{"type": "phone", "value": "13800138000"}
]
}三、常规嵌套字段筛选
对于对象中的对象（如获取 profile.age），可以使用 -> 或 ->> 操作符，以及对应的函数。
1. 使用 -> 和 ->> 操作符
-> 会返回带引号的JSON格式的值，而 ->> 会去除引号返回实际的字符串值。在WHERE条件筛选中，推荐使用 ->> 避免引号带来的类型干扰。
-- 筛选年龄等于28的用户（->> 返回字符串 '28'，MySQL会隐式转换比对） SELECT * FROM users WHERE info->>'$.profile.age' = '28'; -- 筛选技能数组中包含特定元素的查询（获取数组第一个元素） SELECT * FROM users WHERE info->>'$.profile.skills[0]' = 'Java';
2. 使用 JSON_EXTRACT 和 JSON_UNQUOTE 函数
上述操作符本质上是函数的语法糖：-> 等同于 JSON_EXTRACT，->> 等同于 JSON_UNQUOTE(JSON_EXTRACT())。
-- 与 info->>'$.profile.age' = '28' 完全等价 SELECT * FROM users WHERE JSON_UNQUOTE(JSON_EXTRACT(info, '$.profile.age')) = '28';
四、复杂嵌套筛选：数组与对象组合
当需要筛选嵌套数组中的对象属性时（例如：查找联系方式中包含特定邮箱的用户），常规的比较操作符无法直接胜任，需要借助专门的JSON函数。
1. 使用 JSON_CONTAINS 判断包含关系
JSON_CONTAINS(target, candidate[, path]) 用于判断目标文档是否包含给定的值。注意，给定的值必须是合法的JSON格式字符串。
-- 筛选 skills 数组中包含 "MySQL" 的用户 -- 注意：候选值 "MySQL" 必须带双引号，因为它是JSON字符串 SELECT * FROM users WHERE JSON_CONTAINS(info->'$.profile.skills', '"MySQL"');
2. 使用 MEMBER OF (MySQL 8.0.17+)
如果你使用的是MySQL 8.0.17及以上版本，MEMBER OF 提供了更简洁的语法来判断元素是否存在于JSON数组中。
SELECT * FROM users WHERE 'MySQL' MEMBER OF(info->'$.profile.skills');
3. 查询嵌套对象数组中的值
如果要筛选 contacts 数组中，type 为 email 且 value 为 user@test.com 的记录，可以使用 JSON_CONTAINS 配合完整的对象结构匹配：
SELECT * FROM users
WHERE JSON_CONTAINS(
info->'$.contacts',
'{"type": "email", "value": "user@test.com"}'
);五、进阶查询：将嵌套JSON拆解为关系表 (MySQL 8.0+)
对于深层次的嵌套数组遍历与筛选，JSON_TABLE 函数是最强大的工具。它可以将JSON数组直接转换为关系型虚拟表，然后就可以像操作普通表一样进行WHERE筛选和JOIN操作。更多复杂JSON处理方案可以参考 www.ipipp.com 提供的技术文档。
SELECT u.id, ct.type, ct.value FROM users u, JSON_TABLE( u.info, '$.contacts[*]' COLUMNS( type VARCHAR(20) PATH '$.type', value VARCHAR(100) PATH '$.value' ) ) AS ct WHERE ct.type = 'email';
通过 JSON_TABLE，我们提取了 contacts 数组中的每一个对象，将其映射为 ct 表的列，随后即可使用标准的SQL语法 WHERE ct.type = 'email' 进行精准筛选。
六、性能优化建议
虽然MySQL提供了丰富的JSON查询函数，但在JSON列上直接进行条件筛选会导致全表扫描，性能极差。针对高频查询的嵌套字段，推荐以下优化方式：
生成列（Generated Columns）：将需要频繁筛选的嵌套字段提取为生成列，例如将
info->>'$.profile.age'作为一个虚拟列profile_age，然后在该生成列上建立普通索引。避免大JSON：尽量保持JSON字段的体积精简，避免在单个JSON文档中存储成千上万条记录，这会使得解析和查询的内存开销巨大。