Snowflake中解析无命名JSON数组并按键名转列的方法

1. 通过键名而非数组切片取值的方法 #

你可以先展开JSON数组，再将每行的key-value聚合为单个JSON对象，之后就能直接通过键名提取值，完全摆脱数组索引的限制。示例SQL如下：

WITH flattened_data AS (
    SELECT 
        id,
        f.value:key::STRING AS tag_key,
        f.value:value::STRING AS tag_value
    FROM your_table,
         LATERAL FLATTEN(input => json_tag) f
),
aggregated_objects AS (
    SELECT 
        id,
        OBJECT_AGG(tag_key, tag_value) AS tag_object
    FROM flattened_data
    GROUP BY id
)
SELECT 
    id,
    tag_object:"app.name"::STRING AS app_name,
    tag_object:"device.name"::STRING AS device_name,
    tag_object:"os.version"::STRING AS os_version
    -- 按需添加其他需要提取的键名
FROM aggregated_objects;

这里OBJECT_AGG函数会把当前id下的所有标签聚合成一个JSON对象，之后用tag_object:"键名"的语法就能直接取对应值，不管原数组里的键顺序和数量。

2. 实现目标表结构的完整方案 #

上述方法完全可行，下面分两种场景给出落地方案：

场景A：已知所有可能的键名

直接用上面的聚合+键名提取逻辑即可，把所有需要的键名列出来，缺失对应键的行会自动填充NULL，完美适配行与行之间键数量/名称不一致的情况。

场景B：键名未知或动态变化（自动生成列）

Snowflake纯SQL无法动态生成列，但可以通过存储过程实现：

1. 先提取所有唯一的键名：

CREATE OR REPLACE TEMPORARY TABLE unique_keys AS
SELECT DISTINCT f.value:key::STRING AS tag_key
FROM your_table,
     LATERAL FLATTEN(input => json_tag) f;

2. 创建存储过程动态生成宽表SQL：

CREATE OR REPLACE PROCEDURE pivot_json_tags()
RETURNS STRING
LANGUAGE JAVASCRIPT
AS
$$
    const keysCursor = snowflake.execute({sqlText: "SELECT tag_key FROM unique_keys"});
    const columnList = [];
    while (keysCursor.next()) {
        const key = keysCursor.getColumnValue(1);
        // 把带点的键名转成下划线命名的列，避免语法问题
        columnList.push(`tag_object:"${key}"::STRING AS "${key.replace('.', '_')}"`);
    }

    const finalSql = `
        WITH flattened AS (
            SELECT id, f.value:key::STRING k, f.value:value::STRING v
            FROM your_table, LATERAL FLATTEN(input => json_tag) f
        ),
        agg AS (
            SELECT id, OBJECT_AGG(k, v) AS tag_obj
            FROM flattened
            GROUP BY id
        )
        SELECT id, ${columnList.join(', ')} FROM agg;
    `;

    snowflake.execute({sqlText: finalSql});
    return "执行完成，可通过 SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())) 查看结果";
$$;

3. 调用存储过程并查看结果：

CALL pivot_json_tags();
SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

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