Snowflake中动态提取OBJECT键作为列名的SQL实现方法

可以实现，不需要手动指定OBJECT里的所有键，核心是通过FLATTEN展开键值对 + 动态SQL生成PIVOT列来完成转换，具体步骤如下：

1. 先将OBJECT展开为键值对行数据 #

用LATERAL FLATTEN函数把每个OBJECT类型的attributes字段拆成单独的键值对行，同时保留原始的row_id：

SELECT 
    row_id,
    f.key AS attribute_key,
    f.value AS attribute_value
FROM objects_to_pivot
LATERAL FLATTEN(input => attributes) f;

执行后会得到类似这样的结果：

2. 动态生成PIVOT语句实现列转换 #

因为要自动识别所有OBJECT键作为列名，需要先收集所有唯一的键，再拼接成动态SQL执行：

DECLARE
    pivot_cols STRING;
BEGIN
    -- 收集所有唯一的OBJECT键，生成逗号分隔的列列表
    SELECT LISTAGG(DISTINCT attribute_key, ', ') INTO pivot_cols
    FROM (
        SELECT f.key AS attribute_key
        FROM objects_to_pivot
        LATERAL FLATTEN(input => attributes) f
    );

    -- 拼接并执行动态PIVOT查询
    EXECUTE IMMEDIATE '
        SELECT *
        FROM (
            SELECT 
                row_id,
                f.key AS attribute_key,
                f.value AS attribute_value
            FROM objects_to_pivot
            LATERAL FLATTEN(input => attributes) f
        )
        PIVOT (
            MAX(attribute_value) FOR attribute_key IN (' || pivot_cols || ')
        )
        ORDER BY row_id;
    ';
END;

关键说明： #

• 用MAX(attribute_value)作为聚合函数是因为每个row_id+attribute_key只会对应一个值，MAX不会改变结果，也可以替换为ANY_VALUE。
• Snowflake会自动处理不同类型的值（比如数值型的price和字符串型的color），这类混合类型的列会统一转为VARCHAR类型。
• 如果后续表中新增OBJECT键，这个动态SQL会自动将新键转为列，无需修改代码。
• 执行动态SQL需要具备对应的表读写权限以及EXECUTE IMMEDIATE的权限。

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