如何在Snowflake中从VARCHAR类型JSON字符串提取数据为列

核心问题是VARCHAR类型无法直接用FLATTEN函数处理，必须先将其转换为Snowflake支持的半结构化数据类型（ARRAY），再通过展开+行转列实现需求。以下是分步实现方案：

步骤1：将VARCHAR列转为可处理的JSON数组 #

使用TRY_PARSE_JSON()函数把properties列转换为ARRAY类型，该函数能自动处理NULL值或格式错误的JSON，避免查询中断：

SELECT 
    你的其他字段,
    TRY_PARSE_JSON(properties) AS properties_array
FROM 目标表名

步骤2：用LATERAL FLATTEN展开数组 #

基于转换后的ARRAY，通过LATERAL FLATTEN把数组拆分为单行的name-value键值对，用LEFT JOIN保留原表中properties为NULL的记录：

SELECT 
    t.你的其他字段,
    f.value:name::VARCHAR AS prop_name,
    f.value:value::VARCHAR AS prop_value
FROM 目标表名 t
LEFT JOIN LATERAL FLATTEN(INPUT => TRY_PARSE_JSON(t.properties)) f

步骤3：PIVOT行转列（分静态/动态两种场景） #

静态PIVOT（已知所有需要转换的name） #

如果提前明确所有要生成的新列名，直接写死列列表即可：

WITH flattened_data AS (
    SELECT 
        t.主键字段, -- 用于关联原记录的唯一标识，比如ID
        t.你的其他字段,
        f.value:name::VARCHAR AS prop_name,
        f.value:value::VARCHAR AS prop_value
    FROM 目标表名 t
    LEFT JOIN LATERAL FLATTEN(INPUT => TRY_PARSE_JSON(t.properties)) f
)
SELECT *
FROM flattened_data
PIVOT (
    MAX(prop_value) -- 用MAX/ANY_VALUE均可，每个主键+name仅对应一个值
    FOR prop_name IN ('age', 'gender', 'address') -- 替换为实际的name值
) AS p

动态PIVOT（未知所有name，自动生成列） #

如果name的组合不固定，需要动态生成列列表，可通过存储过程实现：

CREATE OR REPLACE PROCEDURE dynamic_pivot_properties()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
    col_list VARCHAR;
    sql_stmt VARCHAR;
BEGIN
    -- 提取所有唯一的prop_name并拼接成列列表
    SELECT LISTAGG(DISTINCT '''' || prop_name || '''', ',') INTO col_list
    FROM (
        SELECT f.value:name::VARCHAR AS prop_name
        FROM 目标表名 t
        LEFT JOIN LATERAL FLATTEN(INPUT => TRY_PARSE_JSON(t.properties)) f
        WHERE prop_name IS NOT NULL
    );

    -- 拼接动态PIVOT执行SQL
    sql_stmt := '
        WITH flattened_data AS (
            SELECT 
                t.主键字段,
                t.你的其他字段,
                f.value:name::VARCHAR AS prop_name,
                f.value:value::VARCHAR AS prop_value
            FROM 目标表名 t
            LEFT JOIN LATERAL FLATTEN(INPUT => TRY_PARSE_JSON(t.properties)) f
        )
        SELECT *
        FROM flattened_data
        PIVOT (
            MAX(prop_value)
            FOR prop_name IN (' || col_list || ')
        ) AS p
    ';

    -- 执行动态SQL
    EXECUTE IMMEDIATE sql_stmt;
    RETURN '动态PIVOT执行完成，生成列：' || col_list;
END;
$$;

-- 调用存储过程
CALL dynamic_pivot_properties();

关键注意事项 #

• 优先用TRY_PARSE_JSON而非PARSE_JSON，避免因JSON格式错误导致整个查询失败
• 如果value是数字、日期等类型，把::VARCHAR替换为对应类型（比如::NUMBER/::DATE）
• PIVOT时使用MAX/ANY_VALUE聚合函数，因为每个原始记录的同一个name只会对应一个值，聚合结果不影响最终数据

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