Snowflake中利用Lateral Flatten实现JSON属性动态转多列

问题背景 #

原始JSON数据格式：

"sales_attributes":[
{
    "id":"100000",
    "name":"Colour",
    "value_id":"77777",
    "value_name":"Red Velvet"
},
{
    "id":"100089",
    "name":"Specification",
    "value_id":"88888",
    "value_name":"Bundle"
}]

需求是把数组中每个元素的name和value_name转换为动态多列，期望输出：

尝试了以下SQL（已修正拼写错误）：

SELECT 
sa.value:name::string as variant_field1,
sa.value:value_name::string as variant_value1,
FROM 
table,
LATERAL FLATTEN(input => sales_attributes) as sa

但得到的是多行结果：

需要实现列数根据JSON数组元素数量动态生成，该如何解决？

解决方案 #

在Snowflake中，静态SQL无法直接实现动态列生成，需要结合动态SQL和条件聚合来完成，步骤如下：

1. 生成动态列定义并拼接执行SQL #

通过动态拼接SQL语句，根据数组元素的数量自动生成对应列，最终用EXECUTE IMMEDIATE执行：

DECLARE
    v_sql STRING;
    v_column_list STRING;
BEGIN
    -- 生成动态列的SQL片段
    SELECT LISTAGG(
            CONCAT(
                'MAX(CASE WHEN rn = ', rn, ' THEN field_name END) AS variant_field', rn, ',',
                'MAX(CASE WHEN rn = ', rn, ' THEN value_name END) AS variant_value', rn
            ),
            ','
        ) WITHIN GROUP (ORDER BY rn)
    INTO v_column_list
    FROM (
        SELECT DISTINCT
            ROW_NUMBER() OVER(PARTITION BY t.id ORDER BY sa.index) AS rn
        FROM your_table t,
        LATERAL FLATTEN(input => t.sales_attributes) sa
    );

    -- 拼接完整查询语句
    v_sql := CONCAT(
        'WITH flattened_data AS (',
        '    SELECT ',
        '        t.id AS row_id,',
        '        sa.value:name::string AS field_name,',
        '        sa.value:value_name::string AS value_name,',
        '        ROW_NUMBER() OVER(PARTITION BY t.id ORDER BY sa.index) AS rn',
        '    FROM your_table t,',
        '    LATERAL FLATTEN(input => t.sales_attributes) sa',
        ')',
        'SELECT row_id, ', v_column_list, ' FROM flattened_data GROUP BY row_id;'
    );

    -- 执行动态SQL
    EXECUTE IMMEDIATE v_sql;
END;

关键说明 #

• 替换your_table为实际表名，id为表中唯一标识每行的字段（若无唯一键，可使用HASH(*)生成临时唯一标识）
• ROW_NUMBER() OVER(PARTITION BY t.id ORDER BY sa.index)用于保证数组元素的顺序和生成列的顺序一致
• 该逻辑会自动适配每行sales_attributes数组的长度，生成对应数量的variant_fieldN和variant_valueN列

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