如何查询Redshift中SUPER类型列内嵌套JSON数据的所有层级键名

核心实现逻辑 #

Redshift原生提供OBJECT_KEYS函数可提取SUPER对象的顶层键数组，搭配递归CTE即可实现任意嵌套层级的键名遍历，自动跳过非对象类型的字段值，适配上游不规整的嵌套结构。

方案1：原生递归CTE（推荐，适配十亿级大数据量） #

该方案全部使用Redshift原生SQL语法，执行性能远高于自定义UDF，适合全表大数量扫描场景。
示例代码如下（假设你的表名为your_table，SUPER类型列名为super_col，主键列为id，不需要关联原记录可删除主键相关逻辑）：

WITH RECURSIVE all_keys AS (
    -- 锚点成员：提取所有记录的顶层键
    SELECT
        id,
        key,
        super_col[key] AS current_val
    FROM your_table,
         UNNEST(OBJECT_KEYS(super_col)) AS t(key)
    UNION ALL
    -- 递归成员：判断当前值为对象则继续下钻提取下一层键
    SELECT
        a.id,
        k.key,
        a.current_val[k.key] AS current_val
    FROM all_keys a,
         UNNEST(OBJECT_KEYS(a.current_val)) AS k(key)
    WHERE IS_OBJECT(a.current_val)
)
-- 去重后返回全表所有唯一键名，无需去重则去掉DISTINCT即可
SELECT DISTINCT key FROM all_keys;

十亿级数据量优化建议 #

• 全量跑之前先抽取1%的样本数据验证逻辑正确性，避免全表扫描出错浪费集群资源
• 若只需统计全表唯一键集合，可在递归CTE的每层都增加DISTINCT过滤，减少向下传递的数据量
• 若表有分区键，可按分区范围分批执行查询，避免单次查询占用过多资源
• Redshift默认递归最大深度为100，若你的嵌套层级超过100，可在会话级别调整参数：SET max_recursion_depth = <实际最大嵌套层级>;
• 若JSON中包含数组类型，可在递归逻辑中补充IS_ARRAY判断，先UNNEST数组元素再继续提取键即可

方案2：Python UDF（仅适合小数据量验证） #

如果仅需小范围抽样验证键名，可自定义PLPythonUDF实现递归提取：

CREATE OR REPLACE FUNCTION f_extract_all_keys(super_obj SUPER)
RETURNS SUPER
IMMUTABLE AS $$
def extract_keys(obj, key_list=None):
    if key_list is None:
        key_list = []
    if isinstance(obj, dict):
        for k, v in obj.items():
            key_list.append(k)
            extract_keys(v, key_list)
    # 若需要处理数组内对象的键，可补充elif isinstance(obj, list)的逻辑遍历数组元素
    return key_list
$$ LANGUAGE plpythonu;

使用示例：

-- 展开返回单条记录的所有键
SELECT key FROM your_table, UNNEST(f_extract_all_keys(super_col)) AS t(key) LIMIT 1;

注意：自定义UDF在十亿行级数据上执行性能远低于原生SQL，不推荐全表扫描使用。

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