Redshift中如何校验列表值是否存在于JSON字段并返回对应路径

该需求完全可以在Redshift中通过SQL实现，核心是通过递归CTE拆解JSON的所有键值对路径，再和待匹配值列表关联过滤即可。

前置条件 #

• 存储JSON的列类型建议为SUPER类型（Redshift原生支持半结构化数据查询的类型），如果是VARCHAR类型的JSON字符串，可以先通过JSON_PARSE()函数转为SUPER类型再处理。

核心实现逻辑 #

• 第一步：构造待匹配值列表，用CTE把待匹配值转成多行数据
• 第二步：用递归CTE遍历JSON的所有层级，拆解出每个值对应的完整路径
• 第三步：将拆解出来的JSON值和待匹配值列表做关联，匹配成功就返回匹配状态和对应路径

示例查询代码 #

WITH RECURSIVE
-- 待匹配值列表，可根据实际需求修改
possible_values AS (
    SELECT 'html' AS val UNION ALL
    SELECT 'php' AS val UNION ALL
    SELECT 'python' AS val
),
-- 示例源数据，可替换为自己的业务表
source_data AS (
    SELECT JSON_PARSE('{"encoding":"ASCI","web":{"code_language":"php"}}') AS json_col
),
-- 递归遍历JSON所有路径
json_paths AS (
    -- 第一层遍历：根节点下的键
    SELECT
        t.key AS path,
        t.value AS val
    FROM source_data,
         UNPIVOT json_col AS t
    UNION ALL
    -- 递归遍历嵌套层级的键
    SELECT
        jp.path || '\\' || t.key AS path,
        t.value AS val
    FROM json_paths jp,
         UNPIVOT jp.val AS t
    WHERE JSON_TYPEOF(jp.val) = 'object'
)
-- 关联匹配返回结果
SELECT
    CASE WHEN COUNT(p.val) > 0 THEN TRUE ELSE FALSE END AS is_match,
    LISTAGG(DISTINCT jp.path, ', ') AS matched_paths
FROM json_paths jp
LEFT JOIN possible_values p ON jp.val::VARCHAR = p.val

上述示例查询执行后返回结果为is_match = TRUE，matched_paths = "web\code_language"，和预期结果一致。

注意事项 #

• 如果JSON包含数组类型节点，可以在递归逻辑中额外增加对JSON_TYPEOF(val) = 'array'的判断，用UNNEST函数拆解数组元素再继续遍历即可。
• 如果JSON层级很深，可以通过执行SET max_recursion_depth = <自定义最大层级数>调整递归深度上限，Redshift默认递归深度为1000，绝大多数场景足够使用。

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