MySQL中如何按JSON数组对象的指定属性值查询数据？

我有一张名为sometable的表，其中包含一个名为jsonval的JSON字段。该字段存储的JSON对象包含programs属性，其值是一个带有id属性的对象数组。我需要查询出jsonval.programs.id等于14的记录。

目前使用以下查询语句可实现需求：

SELECT id, jsonval, JSON_EXTRACT(jsonval, '$.programs[*].id') FROM `sometable` 
WHERE JSON_EXTRACT(jsonval, '$.programs[*].id') LIKE '%"14"%';

这是因为JSON_EXTRACT(jsonval, '$.programs[*].id')会返回存储id的数组的字符串形式，例如：["14","26"]。但我想知道是否存在更优雅的解决方案，比如使用JSON_CONTAINS？

方法1：使用JSON_CONTAINS #

JSON_CONTAINS可以直接检查JSON结构中是否包含指定对象，只需明确匹配路径和目标对象即可：

SELECT id, jsonval 
FROM `sometable`
WHERE JSON_CONTAINS(jsonval, '{"id": "14"}', '$.programs');

注意：如果programs数组里的id是数值类型而非字符串，将"14"改为14即可。

方法2：使用JSON_SEARCH #

JSON_SEARCH用于查找指定值在JSON中的路径，只要返回结果不为NULL，就说明存在匹配记录：

SELECT id, jsonval 
FROM `sometable`
WHERE JSON_SEARCH(jsonval, 'one', '14', null, '$.programs[*].id') IS NOT NULL;

参数说明：

• 'one'表示找到第一个匹配项即停止搜索，换成'all'会返回所有匹配路径，但仅需判断存在性时用'one'效率更高。
• 最后一个参数'$.programs[*].id'指定搜索路径前缀，限定只在programs数组的id属性中查找。

方法3：使用JSON_TABLE（适合复杂场景） #

如果需要对programs数组元素做进一步关联或筛选操作，JSON_TABLE可以将JSON数组转换为关系型表结构，再进行条件过滤：

SELECT t.id, t.jsonval
FROM `sometable` t,
     JSON_TABLE(t.jsonval, '$.programs[*]' COLUMNS (
         program_id VARCHAR(255) PATH '$.id'
     )) jt
WHERE jt.program_id = '14';

这种方式的优势在于可以直接对数组元素进行关系型操作，灵活性更高。

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