MySQL如何查询JSON数组列中指定key为特定值的所有行？

原有SQL错误原因 #

• 列名拼写错误：表中存储JSON的列名为items，原SQL写为item，字段不匹配导致查询失效
• JSON_CONTAINS参数使用错误：该方法要求第二个参数为合法JSON格式值，直接传入普通字符串MH027无法被识别为JSON字符串，同时路径参数的匹配逻辑也不符合JSON_CONTAINS的设计要求

可行实现方案 #

方案1：使用JSON_SEARCH（更推荐，兼容性更好） #

JSON_SEARCH专门用于查找JSON中指定值的路径，只要返回非空结果就说明存在匹配项：

SELECT *
FROM `transactions`
WHERE JSON_SEARCH(`items`, 'one', 'MH027', NULL, '$[*].code') IS NOT NULL;

参数说明：

• 第二个参数'one'：表示找到第一个匹配项就返回，不需要遍历整个JSON，查询效率更高
• 第四个参数NULL：表示不设置转义字符
• 第五个参数'$[*].code'：指定搜索路径为JSON数组下所有元素的code字段

方案2：使用JSON_CONTAINS（符合原有写法思路） #

如果要使用JSON_CONTAINS，需要先把所有code字段提取为JSON数组，再传入JSON格式的目标值进行匹配：

SELECT *
FROM `transactions`
WHERE JSON_CONTAINS(`items`->'$[*].code', '"MH027"');

注意：目标值"MH027"需要包裹双引号，才符合JSON字符串的格式要求，否则无法匹配。

内容的提问来源于stack exchange，提问作者Chargui Taieb