Snowflake SQL解析JSON：提取data数组及flatten语法解惑

一、原代码中TABLE(flatten(input => $2)) var2 WHERE KEY = 'some_key'的作用 #

这段代码是对var1结果中的第2列（即details这个JSON对象）做扁平化处理：

• flatten(input => $2)：Snowflake里用逗号关联TABLE(flatten)时，$N指代左侧表的第N列。var1的列是ts（第1列）、details（第2列），所以$2就是details字段。对JSON对象执行flatten会把每个键值对拆成单独一行，生成KEY（键名）、VALUE（键值）、PATH等列。
• WHERE KEY = 'some_key'：过滤出扁平化后键名为some_key的行，只保留details中some_key对应的那组键值对数据。

二、修改现有查询提取data数组并加入临时表 #

可以在现有CTE后新增一个CTE，对attr2中的data数组做扁平化，把数组里的每个对象展开为单独行，最终整合到临时表中。修改后的SQL如下：

CREATE TEMP TABLE temp_table AS
(
    WITH 
    var1 AS (
        SELECT ts
            ,col1 AS details
        FROM raw_temp_table
            ,TABLE (flatten(input => $1, OUTER => TRUE)) var1
    ),
    var2 AS (
        SELECT var1.ts
            ,col1 AS details
            ,details:"json_attr1" AS attr1
            ,details:"json_attr2" AS attr2
        FROM var1
            ,TABLE (flatten(input => $2, OUTER => TRUE)) var2
        WHERE KEY = 'some_key'
    ),
    -- 新增CTE：扁平化attr2里的data数组
    var3 AS (
        SELECT 
            var2.ts,
            var2.details,
            var2.attr1,
            var2.attr2,
            -- 展开data数组中的每个对象，命名为data_item
            flatten_val.value AS data_item
        FROM var2
            -- 对attr2中的data数组执行扁平化，OUTER TRUE确保原数据行不丢失（即使data为空）
            ,TABLE (flatten(input => var2.attr2:"data", OUTER => TRUE)) flatten_val
    )
    SELECT * FROM var3
);

说明： #

• 新增的var3中，flatten(input => var2.attr2:"data")直接定位到attr2下的data数组，将数组中的每个对象拆分为单独行，生成的value列就是数组里的单个对象，这里命名为data_item。
• 最终临时表temp_table会包含原有字段（ts、details、attr1、attr2），加上data_item字段（存储data数组中的单个对象）。如果data数组有N个对象，对应原行会拆成N行，每行对应一个数组对象。

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