如何使用Snowflake函数动态扁平化JSON并重组输出结构？

静态场景：已知所有JSON键 #

如果你的JSON对象的键是固定的（比如确定只有empname和empid），可以直接用PIVOT合并键值对为结构化行：

SELECT 
    a.*, -- 保留原表的其他字段
    p."empname",
    p."empid"
FROM table a
LATERAL FLATTEN(input => a.variant_column) f
PIVOT (
    MAX(f.value) FOR f.key IN ('empname', 'empid')
) p;

这里用MAX()是因为PIVOT必须搭配聚合函数，而每个JSON对象的单个键只会对应一个值，所以MAX/MIN/ANY_VALUE都可以，不会影响最终结果。

动态场景：JSON键不固定 #

如果JSON的键是动态变化的，无法提前硬编码，可通过动态SQL+存储过程自动识别所有键并完成扁平化：

1. 创建存储过程自动生成并执行动态PIVOT语句：

CREATE OR REPLACE PROCEDURE flatten_dynamic_json()
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
AS
$$
    // 提取所有唯一的JSON键
    const keyCursor = snowflake.execute({
        sqlText: "SELECT DISTINCT key FROM table a, LATERAL FLATTEN(input => variant_column)"
    });
    const keyList = [];
    while (keyCursor.next()) {
        keyList.push(`'${keyCursor.getColumnValue(1)}'`);
    }
    
    // 拼接动态PIVOT查询语句
    const dynamicSql = `
        SELECT *
        FROM table a
        LATERAL FLATTEN(input => variant_column) f
        PIVOT (
            MAX(f.value) FOR f.key IN (${keyList.join(', ')})
        ) p
    `;
    
    // 执行查询
    snowflake.execute({sqlText: dynamicSql});
    return `已执行动态扁平化，生成的SQL语句：\n${dynamicSql}`;
$$;

2. 调用存储过程完成转换：

CALL flatten_dynamic_json();

注意事项 #

• 确保variant_column存储的是单个JSON对象（而非数组），如果是数组，需要先FLATTEN数组，再处理每个数组元素里的对象。
• 若JSON键包含特殊字符，查询结果的列名会自动带双引号，使用时注意用双引号引用列名。

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