如何在MariaDB的JSON列中筛选符合特定属性的数据集？

问题分析 #

直接使用JSON_VALUE/JSON_SEARCH无法正确遍历JSON数组元素，这类函数需要精确的JSON路径，而你的Values是数组类型，无法通过单一路径匹配数组中任意位置的对象属性，因此返回NULL。

解决方案 #

方法1：使用EXISTS结合JSON_TABLE #

通过JSON_TABLE将JSON数组展开为临时行，再用EXISTS分别验证两个条件是否满足：

SELECT t.*
FROM Tests t
WHERE EXISTS (
    SELECT 1
    FROM JSON_TABLE(
        t.JObject,
        '$.Values[*]' COLUMNS(
            `Key` VARCHAR(255) PATH '$.Key',
            `Value` VARCHAR(255) PATH '$.Value'
        )
    ) j
    WHERE `Key` = 'firstname' AND `Value` LIKE '%ax%'
)
AND EXISTS (
    SELECT 1
    FROM JSON_TABLE(
        t.JObject,
        '$.Values[*]' COLUMNS(
            `Key` VARCHAR(255) PATH '$.Key',
            `Value` VARCHAR(255) PATH '$.Value'
        )
    ) j
    WHERE `Key` = 'city' AND `Value` LIKE '%burg%'
);

方法2：使用JOIN+GROUP BY+HAVING #

将JSON数组展开后按原表记录分组，通过统计满足条件的元素数量确认两个条件都成立：

SELECT t.*
FROM Tests t
JOIN JSON_TABLE(
    t.JObject,
    '$.Values[*]' COLUMNS(
        `Key` VARCHAR(255) PATH '$.Key',
        `Value` VARCHAR(255) PATH '$.Value'
    )
) j ON TRUE
GROUP BY t.Id, t.JObject
HAVING SUM(CASE WHEN `Key` = 'firstname' AND `Value` LIKE '%ax%' THEN 1 ELSE 0 END) > 0
   AND SUM(CASE WHEN `Key` = 'city' AND `Value` LIKE '%burg%' THEN 1 ELSE 0 END) > 0;

说明 #

• JSON_TABLE是MariaDB 10.2+支持的功能，能将JSON数组转换为关系型表结构，让你可以用常规SQL语法筛选数据。
• 两种方法都会返回你期望的Id=2的记录，因为它同时满足firstname匹配%ax%、city匹配%burg%的条件。

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