如何在Snowflake中扁平化嵌套JSON数组并提取子数组到单独表

前提说明 #

假设你已将JSON加载到名为json_source_table的表中，存储JSON的Variant字段为raw_json。

具体操作步骤 #

• 第一步：展开数组并提取数据
  使用Snowflake的FLATTEN函数，先展开父级的ParentArray，再嵌套展开子级的ChildArray，同时保留父ID用于关联：

  SELECT
      parent.value:ParentId::STRING AS parent_id,
      child.value:id::STRING AS child_id,
      child.value:value::STRING AS child_value
  FROM
      json_source_table,
      LATERAL FLATTEN(input => raw_json:ParentArray) AS parent,
      LATERAL FLATTEN(input => parent.value:ChildArray) AS child;
  

• 第二步：持久化到单独表
  如果需要把提取的数据存到独立表，先创建目标表再插入数据：

  -- 创建子表
  CREATE TABLE child_array_table (
      parent_id STRING,
      child_id STRING,
      child_value STRING
  );
  
  -- 插入提取结果
  INSERT INTO child_array_table
  SELECT
      parent.value:ParentId::STRING AS parent_id,
      child.value:id::STRING AS child_id,
      child.value:value::STRING AS child_value
  FROM
      json_source_table,
      LATERAL FLATTEN(input => raw_json:ParentArray) AS parent,
      LATERAL FLATTEN(input => parent.value:ChildArray) AS child;
  

关键提醒 #

• 确认FLATTEN的input路径和JSON层级完全匹配，比如raw_json:ParentArray对应根节点下的ParentArray数组
• 根据实际数据类型调整字段转换规则，比如value如果是数字就用::NUMBER，日期用::DATE
• 若原JSON存在空数组，FLATTEN会自动跳过，不会生成空行

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