MySQL 5.7中如何以JSON数组值为条件查询数据行

假设你的t_offices表数据如下：

id    name           offices
2     london     {"officeIds": [1, 33, 13, 1789]}
3     bangkok    {"officeIds": [2, 3, 40, 19]}

要筛选出officeIds数组中包含值33的行，可使用以下两种常用方法：

方法1：使用JSON_CONTAINS函数 #

JSON_CONTAINS是MySQL中专门用于判断JSON结构是否包含指定值的函数，语法简洁直接：

SELECT * 
FROM t_offices 
WHERE JSON_CONTAINS(offices, '33', '$.officeIds');

参数说明：

• 第一个参数offices是存储JSON的列名
• 第二个参数'33'是要查找的目标值（以字符串形式传入，可匹配JSON中的数值类型）
• 第三个参数'$.officeIds'指定要检索的JSON路径，即目标数组所在位置

方法2：使用JSON_SEARCH函数 #

JSON_SEARCH会返回目标值在JSON中的路径，未找到则返回NULL，通过判断返回值不为空来筛选行：

SELECT * 
FROM t_offices 
WHERE JSON_SEARCH(offices, 'one', 33, NULL, '$.officeIds[*]') IS NOT NULL;

参数说明：

• 'one'表示只返回第一个匹配的路径
• 33是要查找的目标值
• '$.officeIds[*]'指定在officeIds数组的所有元素中查找

以上两种方法都能得到目标结果：

id    name    offices
2     london  {"officeIds": [1, 33, 13, 1789]}

内容的提问来源于stack exchange，提问作者Anubhav Sinha