如何在Snowflake中使用SQL解析表内列表字段并拆分为独立行

正确实现方式 #

假设你的源表名为source_table，分两种字段类型场景处理：

场景1：VALUE字段本身为ARRAY类型 #

直接执行以下SQL即可得到预期结果：

SELECT 
  t.ID,
  f.VALUE AS VALUE,
  t.KEY,
  t.DATE,
  t.USER
FROM source_table t,
LATERAL FLATTEN(input => t.VALUE) f;

场景2：VALUE字段为字符串格式存储的JSON数组 #

需要先将字符串转为JSON数组再拆分：

SELECT 
  t.ID,
  f.VALUE AS VALUE,
  t.KEY,
  t.DATE,
  t.USER
FROM source_table t,
LATERAL FLATTEN(input => PARSE_JSON(t.VALUE)) f;

逻辑说明 #

• LATERAL FLATTEN会自动把数组内的每个元素拆分为独立行
• 默认配置下FLATTEN会自动过滤空数组，不需要额外写条件排除VALUE为空的记录，正好匹配你的需求
• 如果执行时报字段名相关错误，可对KEY、USER这类SQL关键字段名加双引号包裹，例如t."KEY"

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