Snowflake平台嵌套JSON的Flatten展平与目标结构JSON重构实现

核心实现路径 #

Snowflake处理这类嵌套JSON展平，完全依托原生半结构化数据能力即可实现，不需要额外开发自定义UDF：

• 用LATERAL FLATTEN函数拆解result列里的catalog数组，数组内每一个对象元素会自动生成独立的结果行
• 直接从result的根节点层级提取全局的datetime、catalog_id字段，这些字段值会自动关联到对应每一条展平后的子行，不需要手动做关联匹配
• 从展平后的子元素对象中提取img_href字段，映射为目标结构要求的url属性

可直接复用的参考SQL #

假设存储原始JSON的表为event_raw，result字段为VARIANT类型存储原始JSON：

SELECT
  -- 根层级全局字段，每行自动关联原始值
  result:datetime::TIMESTAMP AS global_datetime,
  result:catalog_id::STRING AS catalog_id,
  -- 展平后子元素字段，按目标结构映射
  f.value:img_href::STRING AS url,
  f.value:品类名称::STRING AS category_name,
  f.value:价格::FLOAT AS price
FROM event_raw,
-- 侧向外连接展平catalog数组
LATERAL FLATTEN(
  input => result:catalog,
  outer => TRUE,
  recursive => FALSE
) f

常见踩坑注意事项 #

• 展平参数一定要配置outer => TRUE：如果某条原始记录的catalog字段为NULL或者空数组，不会被直接过滤丢弃，会保留原始行，对应子元素字段值为NULL，避免业务数据丢失
• 所有从VARIANT里提取的字段必须显式加类型转换（::目标类型）：否则返回值都是VARIANT类型，后续做过滤、关联、计算时会产生额外的类型转换开销，甚至出现隐式匹配失败的问题
• 如果最终输出要求是重构为指定结构的对象数组，可以在上述展平查询的外层嵌套一层聚合函数，用ARRAY_AGG+OBJECT_CONSTRUCT组装目标结构，示例片段：

  SELECT
    ARRAY_AGG(
      OBJECT_CONSTRUCT(
        'global_datetime', global_datetime,
        'url', url
      )
    ) AS target_struct_array
  FROM 上述展平查询的别名
  

  其中加recursive => FALSE参数是为了只展平第一层的catalog数组，避免把数组元素里的其他深层嵌套数组也误展平。

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