在Snowflake中实现JSON转CSV的Pivot Data Down而非扁平化转换

核心思路 #

通过LATERAL FLATTEN展开嵌套数组，同时关联父级对象的所有字段，最终将结果导出为CSV，实现与convertcsv网站"Pivot data down instead of flattening"一致的输出——即数组的每个元素单独占一行，父级字段重复填充至对应行。

示例实现 #

假设你的JSON数据存储在表your_table的json_data字段中，结构如下：

{
  "user_id": "U001",
  "user_name": "John Doe",
  "purchases": [
    {"product_id": "P001", "price": 29.99, "quantity": 2},
    {"product_id": "P002", "price": 15.50, "quantity": 1},
    {"product_id": "P003", "price": 49.99, "quantity": 1}
  ]
}

1. 解析并展开JSON #

先用PARSE_JSON将字符串类型的JSON转换为Snowflake半结构化数据类型，再通过LATERAL FLATTEN展开嵌套数组，同时保留所有父级字段：

SELECT
  -- 提取父级对象字段
  json_data:user_id::STRING AS user_id,
  json_data:user_name::STRING AS user_name,
  -- 提取数组元素中的字段
  flattened.value:product_id::STRING AS product_id,
  flattened.value:price::FLOAT AS price,
  flattened.value:quantity::INT AS quantity
FROM
  your_table,
  LATERAL FLATTEN(input => PARSE_JSON(json_data):purchases) AS flattened

2. 导出为CSV #

方式1：Web UI直接下载

执行上述查询后，在Snowflake Web UI的查询结果右上角点击「Download」，选择「CSV」格式即可获取文件。

方式2：批量导出到外部存储

使用COPY INTO命令将结果导出到指定外部存储（如S3、Azure Blob等）：

COPY INTO @your_external_stage/pivot_output.csv
FROM (
  SELECT
    json_data:user_id::STRING AS user_id,
    json_data:user_name::STRING AS user_name,
    flattened.value:product_id::STRING AS product_id,
    flattened.value:price::FLOAT AS price,
    flattened.value:quantity::INT AS quantity
  FROM
    your_table,
    LATERAL FLATTEN(input => PARSE_JSON(json_data):purchases) AS flattened
)
FILE_FORMAT = (TYPE = CSV FIELD_OPTIONALLY_ENCLOSED_BY = '"' HEADER = TRUE)

关键注意事项 #

• 多层嵌套数组：若JSON存在多层嵌套数组，需逐层使用LATERAL FLATTEN展开，确保每一层数组元素对应单独一行，同时保留所有上层父级字段。
• 空数组处理：若数组可能为空，添加OUTER关键字（LATERAL OUTER FLATTEN），确保父级字段仍能输出为一行（数组字段为NULL）。
• 类型匹配：字段转换需严格对应JSON数据类型（如::STRING、::FLOAT），避免类型不匹配报错。

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