如何在Snowflake中展平无键值对的嵌套数组数据

针对你这种第一个子数组作为列名、后续子数组作为行数据的JSON数组格式，可通过Snowflake的数组函数+动态SQL实现展平，具体步骤如下：

1. 拆分表头与数据行 #

首先从存储的数组中分离出表头（第一个子数组）和所有数据行（从第二个子数组开始的部分）：

• 提取表头：使用ARRAY_SLICE截取数组的第一个元素
• 提取数据行：使用LATERAL FLATTEN展开从第二个元素开始的所有子数组，得到每行的独立数组

2. 动态生成列映射SQL #

由于列名是动态的（来自第一个子数组），需要先拼接出对应列的映射语句，再执行动态SQL完成展平：

步骤1：生成列定义语句 #

先查询并拼接出每个表头列对应的数据提取逻辑：

SELECT LISTAGG('GET(row_array, ' || idx || ') AS "' || value || '"', ', ') AS column_defs
FROM (
    SELECT value, INDEX(idx) idx
    FROM data_table,
         LATERAL FLATTEN(INPUT => ARRAY_SLICE(data_array, 0, 1)[0])
);

• ARRAY_SLICE(data_array, 0, 1)[0]：取出第一个子数组作为表头
• FLATTEN展开表头数组，INDEX(idx)获取每个表头元素的索引（对应数据行数组的位置）
• LISTAGG拼接成GET(row_array, 0) AS "col1", GET(row_array, 1) AS "col2"这样的列定义字符串

步骤2：执行动态SQL完成展平 #

将上述生成的列定义代入最终查询，执行动态SQL：

-- 存储列定义到变量
SET column_defs = (
    SELECT LISTAGG('GET(row_array, ' || idx || ') AS "' || value || '"', ', ')
    FROM (
        SELECT value, INDEX(idx) idx
        FROM data_table,
             LATERAL FLATTEN(INPUT => ARRAY_SLICE(data_array, 0, 1)[0])
    )
);

-- 组装最终查询语句
SET final_sql = '
SELECT ' || $column_defs || '
FROM data_table,
     LATERAL FLATTEN(INPUT => ARRAY_SLICE(data_array, 1)) f
';

-- 执行查询
EXECUTE IMMEDIATE $final_sql;

• ARRAY_SLICE(data_array, 1)：截取从第二个元素开始的所有子数组（即数据行）
• LATERAL FLATTEN展开每个数据行数组，GET(row_array, idx)根据表头索引提取对应位置的元素，映射为指定列名

示例效果 #

假设你的data_array是[["col1","col2","col3"],["a1","a2","a3"],["b1","b2","b3"]]，执行后会得到如下结果：

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