如何在Snowflake中展平含多数组的JSON并提取关联数据？

问题核心是三个数组（ItemCountGroup、ItemDescGroup、ItemTypeGroup）是按索引一一对应的，直接分别FLATTEN会产生笛卡尔积（所有元素无差别组合），正确做法是利用FLATTEN返回的索引关联对应位置的元素。

假设你的表结构： #

• 表名：order_json
• 存储JSON的VARIANT字段：json_data

实现SQL： #

SELECT
    json_data:ordernumber::STRING AS order_number,
    -- 从ItemCountGroup取当前索引的元素
    flattened_item_count.value::INT AS item_count,
    -- 通过索引取ItemDescGroup对应位置的元素
    json_data:ItemDescGroup[flattened_item_count.index]::STRING AS item_desc,
    -- 通过索引取ItemTypeGroup对应位置的元素
    json_data:ItemTypeGroup[flattened_item_count.index]::STRING AS item_type
FROM
    order_json,
    LATERAL FLATTEN(input => json_data:ItemCountGroup) AS flattened_item_count
-- 可选：过滤三个数组长度不一致的异常数据
WHERE
    ARRAY_SIZE(json_data:ItemCountGroup) = ARRAY_SIZE(json_data:ItemDescGroup)
    AND ARRAY_SIZE(json_data:ItemCountGroup) = ARRAY_SIZE(json_data:ItemTypeGroup);

关键说明： #

• LATERAL FLATTEN展开ItemCountGroup时，会返回每个元素的value和对应的index（元素在数组中的位置，从0开始计数）。
• 利用index访问另外两个数组的对应位置元素，保证三个字段属于同一组关联数据，避免笛卡尔积。
• 可选的WHERE条件用于排除数组长度不匹配的异常JSON，防止出现NULL或错位数据。

将结果存入单独表： #

基于上述查询创建目标表：

CREATE TABLE order_items AS
SELECT
    json_data:ordernumber::STRING AS order_number,
    flattened_item_count.value::INT AS item_count,
    json_data:ItemDescGroup[flattened_item_count.index]::STRING AS item_desc,
    json_data:ItemTypeGroup[flattened_item_count.index]::STRING AS item_type
FROM
    order_json,
    LATERAL FLATTEN(input => json_data:ItemCountGroup) AS flattened_item_count
WHERE
    ARRAY_SIZE(json_data:ItemCountGroup) = ARRAY_SIZE(json_data:ItemDescGroup)
    AND ARRAY_SIZE(json_data:ItemCountGroup) = ARRAY_SIZE(json_data:ItemTypeGroup);

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