如何解析含任意键的JSON数据并编写SQL查询实现规则条件校验

可以实现，主流支持JSON函数的数据库都提供了遍历JSON对象未知键的能力，以下是可落地的实现方案：

方案1：SQL原生遍历JSON键（推荐） #

该方案性能较高，适配主流高版本数据库。

MySQL 8.0+ 实现 #

使用JSON_TABLE将rules对象下的所有子项展开为行数据后过滤：

SELECT t.*
FROM 你的表名 t
WHERE EXISTS (
    SELECT 1
    FROM JSON_TABLE(
        t.json列名->'$.rules', -- 提取rules根对象
        '$.*' COLUMNS (
            results JSON PATH '$.results',
            is_test_mode BOOLEAN PATH '$.isTestMode'
        )
    ) AS rule_items
    WHERE 
        JSON_ARRAY_LENGTH(rule_items.results) >= 1 -- 保证results至少有一个元素
        AND JSON_UNQUOTE(JSON_EXTRACT(rule_items.results, '$[0].required')) = 'true'
        AND rule_items.is_test_mode = FALSE
)

PostgreSQL 实现 #

如果JSON列是jsonb类型，使用jsonb_each遍历键值对：

SELECT t.*
FROM 你的表名 t
WHERE EXISTS (
    SELECT 1
    FROM jsonb_each(t.json列名 -> 'rules') AS rule_items(rule_key, rule_value)
    WHERE 
        jsonb_array_length(rule_value -> 'results') >= 1
        AND (rule_value -> 'results' -> 0 ->> 'required')::boolean = true
        AND (rule_value ->> 'isTestMode')::boolean = false
)

如果是Spark SQL、Hive等查询引擎，也可以通过explode + from_json的组合实现相同逻辑。

方案2：低版本兼容正则兜底方案 #

如果你的数据库不支持JSON展开函数，可以用正则匹配实现，仅适合临时校验场景，性能较差：

SELECT *
FROM 你的表名
WHERE json列名 REGEXP '"isTestMode":\\s*false.*?"results":\\s*\\[\\s*\\{\\s*"required":\\s*true'
-- 可根据你实际JSON的字段生成顺序调整正则顺序，避免漏匹配

注意事项 #

• 原生JSON函数方案性能远高于正则匹配，表数据量大时建议给JSON列创建对应JSON索引（如MySQL的多值索引、PostgreSQL的GIN索引）
• 如果业务上需要频繁查询规则字段，建议将rules下的规则数据抽为独立关系表存储，查询性能和可维护性都会大幅提升

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