如何在Snowflake中无需逐个指定Key提取Variant列JSON所有一级字段

针对你需要提取Variant列中所有JSON一级字段（排除_acl、accounts等嵌套字段）的需求，无需逐个手动指定Key，可通过以下两种方法实现：

方法1：动态SQL自动生成提取语句 #

通过查询获取所有有效一级Key，自动拼接生成完整的SELECT语句，避免手动编写130+字段：

-- 定义需要排除的字段
SET exclude_keys = '(''_acl'', ''accounts'')';

-- 生成所有需要提取的字段列表
SET column_list = (
    SELECT LISTAGG('SAC.JSON:' || key || '::STRING AS ' || UPPER(key), ', ')
    FROM (
        SELECT DISTINCT js.key
        FROM DEV_ODS_SOFINORD.ODS.CRM_JSON_VARIANT_ALL SAC,
        TABLE(FLATTEN(INPUT => SAC.JSON)) js
        WHERE js.key NOT IN $exclude_keys
    )
);

-- 生成并执行完整查询
SET query = 'SELECT SAC.ORIGINE, SAC.DATE_CHARGEMENT, ' || $column_list || ' FROM DEV_ODS_SOFINORD.ODS.CRM_JSON_VARIANT_ALL SAC';

EXECUTE IMMEDIATE $query;

说明：如果JSON值有不同数据类型（如数字、日期），可将::STRING替换为对应类型（如::NUMBER、::TIMESTAMP），或先保留::VARIANT再按需转换。

方法2：FLATTEN+PIVOT转换为宽表 #

先将JSON拆分为键值对，过滤掉无关字段后，通过PIVOT转换为标准列结构：

-- 定义需要排除的字段
SET exclude_keys = '(''_acl'', ''accounts'')';

-- 生成PIVOT所需的列名列表
SET pivot_columns = (
    SELECT LISTAGG('''' || key || '''', ', ')
    FROM (
        SELECT DISTINCT js.key
        FROM DEV_ODS_SOFINORD.ODS.CRM_JSON_VARIANT_ALL SAC,
        TABLE(FLATTEN(INPUT => SAC.JSON)) js
        WHERE js.key NOT IN $exclude_keys
    )
);

-- 执行FLATTEN+PIVOT查询
SET query = '
SELECT *
FROM (
    SELECT 
        SAC.ORIGINE,
        SAC.DATE_CHARGEMENT,
        js.key,
        js.value::STRING AS value
    FROM DEV_ODS_SOFINORD.ODS.CRM_JSON_VARIANT_ALL SAC,
    TABLE(FLATTEN(INPUT => SAC.JSON)) js
    WHERE js.key NOT IN $exclude_keys
)
PIVOT (
    MAX(value) FOR key IN (' || $pivot_columns || ')
) AS p';

EXECUTE IMMEDIATE $query;

说明：使用MAX(value)是因为每个键在单条JSON中只会出现一次，聚合函数不影响结果，符合PIVOT语法要求。

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