在Snowflake SQL中如何自动将JSON字段提取拆分为多个新列？

核心思路 #

通过Snowflake内置的FLATTEN函数动态提取所有JSON键，结合动态SQL自动生成列提取逻辑，无需手动枚举每个字段。 #

实现步骤 #

1. 基础数据预处理 #

先将原始JSON字符串列转换为Snowflake支持的VARIANT半结构化类型：

WITH parsed_data AS (
    SELECT 
        ID,
        Name,
        PARSE_JSON(Value) AS json_val -- 转换为VARIANT类型
    FROM 你的原始表名 -- 替换为你实际的表名
)

2. 单层级JSON自动拆分（适配你的示例场景） #

执行以下代码直接生成并运行拆列查询，即可得到预期输出：

-- 自动收集所有JSON键，生成动态查询语句
SET @dynamic_query = (
    SELECT 
        'SELECT ID, Name, ' || 
        LISTAGG(DISTINCT 'json_val:' || key || ' AS ' || key, ', ') || 
        ' FROM parsed_data'
    FROM parsed_data,
         LATERAL FLATTEN(INPUT => json_val) -- 打平JSON提取所有键
);

-- 执行动态查询得到结果
EXECUTE IMMEDIATE @dynamic_query;

3. 嵌套JSON自动拆分（支持子层级字段） #

如果你的JSON包含嵌套对象，开启递归打平模式即可自动提取所有层级的字段：

SET @dynamic_query = (
    SELECT 
        'SELECT ID, Name, ' || 
        LISTAGG(DISTINCT 'json_val' || PATH || ' AS ' || REGEXP_REPLACE(PATH, '[\\.\\[\\]]', '_'), ', ') || 
        ' FROM parsed_data'
    FROM parsed_data,
         LATERAL FLATTEN(INPUT => json_val, RECURSIVE => TRUE) -- 递归打平所有嵌套层级
    WHERE TYPEOF(VALUE) NOT IN ('OBJECT', 'ARRAY') -- 仅提取最终值字段，跳过父级对象/数组
);

EXECUTE IMMEDIATE @dynamic_query;

4. 结果持久化（可选） #

如果需要把拆分后的结果永久保存为新表，修改动态语句为建表逻辑即可：

SET @create_table_sql = (
    SELECT 
        'CREATE OR REPLACE TABLE 拆分后的新表名 AS SELECT ID, Name, ' || 
        LISTAGG(DISTINCT 'json_val:' || key || ' AS ' || key, ', ') || 
        ' FROM parsed_data'
    FROM parsed_data,
         LATERAL FLATTEN(INPUT => json_val)
);

EXECUTE IMMEDIATE @create_table_sql;

注意事项 #

• 若JSON键名包含特殊字符，可在生成列名时用双引号包裹，避免语法报错
• 数据量较大时，可先抽样小批量数据提取全量键，再生成全量查询降低性能开销
• 若需要拆分数组类型字段，可额外添加MODE => 'ARRAY'参数到FLATTEN函数中调整逻辑

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