如何在MariaDB中编写满足特定条件的复杂JSON查询？

可以用MariaDB的JSON_TABLE结合其他JSON函数实现这个逻辑，以下是满足需求的查询语句：

SELECT id, data
FROM `table`
WHERE EXISTS (
    SELECT 1
    FROM JSON_TABLE(
        `table`.data->'$.items',
        '$[*]' COLUMNS (
            item JSON PATH '$'
        )
    ) AS items_unpacked
    WHERE JSON_CONTAINS_PATH(items_unpacked.item, 'one', '$.foo') = 1
    AND EXISTS (
        SELECT 1
        FROM JSON_TABLE(
            JSON_KEYS(items_unpacked.item->'$.foo'),
            '$[*]' COLUMNS (
                foo_key VARCHAR(255) PATH '$'
            )
        ) AS foo_keys_unpacked
        WHERE JSON_EXTRACT(items_unpacked.item, CONCAT('$.foo.', foo_keys_unpacked.foo_key)) > 0
    )
);

逻辑拆解 #

• 外层EXISTS子查询：检查当前行的data列中是否存在符合条件的items元素
• 第一层JSON_TABLE：把items数组拆分成单独的行记录，每一行对应数组里的一个item对象
• JSON_CONTAINS_PATH判断foo存在：JSON_CONTAINS_PATH(item, 'one', '$.foo') = 1筛选出包含foo键的item
• 第二层JSON_TABLE处理foo的键：用JSON_KEYS获取foo对象的所有键，再通过JSON_TABLE把这些键拆成行
• 判断foo的值是否大于0：通过JSON_EXTRACT根据键名动态获取对应的值，只要存在一个值大于0，内层EXISTS就会返回true

关键函数说明 #

• JSON_TABLE：将JSON数组/对象转换为关系型表格结构，是遍历JSON数组的核心工具
• JSON_CONTAINS_PATH：检查JSON文档中是否存在指定路径的键，'one'表示只要存在任意一个匹配路径即可
• JSON_KEYS：获取JSON对象的所有键，返回键组成的数组
• JSON_EXTRACT：根据路径提取JSON中的值，这里通过拼接路径字符串实现动态获取foo下每个键的值

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