MySQL中如何对JSON列指定键的对应值进行通配符搜索？

问题描述 #

数据库中有三条metadata字段的JSON数据：

[{"key": "field_1", "value": "test_1"}, {"key": "field_2", "value": "test_2"}]

[{"key": "field_1", "value": "1"}, {"key": "field_2", "value": "test_2"}]

[{"key": "field_2", "value": "test_2"}, {"key": "field_1", "value": "test_1"}]

需求是筛选出**包含"key": "field_1"且该条目对应的"value"匹配通配符%est%**的数据。

之前尝试的SQL语句：

select *
from `table`
where json_contains(metadata, '[{"key": "field_1"}]') and json_search(metadata, 'one', '%est%', null, '$[*].value') is not null

结果三条数据都被查出，问题在于json_search会搜索数组中所有元素的value，哪怕是field_2的value符合条件，也会导致误匹配。

解决方案 #

方案一：用JSON_TABLE拆分JSON数组 #

将JSON数组转换为行结构，精准筛选目标key对应的value：

SELECT t.*
FROM `table` t
JOIN JSON_TABLE(
    t.metadata,
    '$[*]' COLUMNS(
        `key` VARCHAR(50) PATH '$.key',
        `value` VARCHAR(50) PATH '$.value'
    )
) jt ON jt.`key` = 'field_1'
WHERE jt.`value` LIKE '%est%';

这个方法把每条JSON数组拆成独立的key-value行，只关联key为field_1的行，再过滤value匹配通配符的记录，最终只会返回第一条和第三条符合要求的数据。

方案二：精准指定JSON_SEARCH的搜索路径 #

利用JSON路径的条件过滤功能，只搜索key为field_1的元素的value：

SELECT *
FROM `table`
WHERE JSON_SEARCH(metadata, 'one', '%est%', null, '$[*]?(@.key == "field_1").value') IS NOT NULL;

路径表达式$[*]?(@.key == "field_1").value的意思是：遍历数组中所有满足key等于field_1的元素，仅对这些元素的value进行通配符搜索，避免了其他key的value干扰。

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