如何在MariaDB中基于JSON列内的条件查询符合要求的记录？

• 原JSON中sys_age的value是字符串格式"[3,10]"，无法直接与数字进行范围比较
• 原SQL硬指定数组索引（$[0]、$[1]），一旦条件顺序变化，查询直接失效

1. 修改JSON数据结构 #

把between条件的value从字符串改为数值数组，修改后的sys_conditions示例：

[{"column":"sys_active","value":true,"operator":"="},{"column":"sys_age","value":[3,10],"operator":"between"}]

2. 编写正确的查询语句 #

使用JSON_TABLE将JSON条件数组转换为关系型行数据，再根据条件类型匹配目标值（此处目标值为5）：

匹配单个条件示例 #

SELECT p.*
FROM permissions p
JOIN JSON_TABLE(
    p.sys_conditions,
    '$[*]' COLUMNS(
        column_name VARCHAR(50) PATH '$.column',
        operator VARCHAR(10) PATH '$.operator',
        value_range_start INT PATH '$.value[0]',
        value_range_end INT PATH '$.value[1]'
    )
) jt ON 1=1
WHERE p.sys_conditions IS NOT NULL
AND jt.column_name = 'sys_age' 
AND jt.operator = 'between' 
AND 5 BETWEEN jt.value_range_start AND jt.value_range_end;

同时满足所有条件示例 #

如果需要确保sys_conditions中的所有条件都被满足，可通过分组聚合验证：

SELECT p.*
FROM permissions p
JOIN JSON_TABLE(
    p.sys_conditions,
    '$[*]' COLUMNS(
        column_name VARCHAR(50) PATH '$.column',
        operator VARCHAR(10) PATH '$.operator',
        value_bool BOOL PATH '$.value',
        value_range_start INT PATH '$.value[0]',
        value_range_end INT PATH '$.value[1]'
    )
) jt ON 1=1
WHERE p.sys_conditions IS NOT NULL
AND (
    (jt.column_name = 'sys_age' AND jt.operator = 'between' AND 5 BETWEEN jt.value_range_start AND jt.value_range_end)
    OR (jt.column_name = 'sys_active' AND jt.operator = '=' AND jt.value_bool = true)
)
GROUP BY p.id -- 假设permissions表主键为id
HAVING COUNT(*) = JSON_LENGTH(p.sys_conditions);

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