如何在Snowflake中查询VARIANT类型JSON数组并聚合输出列表

最优解决方案 #

Snowflake 原生提供了数组转换函数 TRANSFORM，可以直接对 VARIANT 类型的数组元素做批量处理，不需要拆分数组再聚合，性能远高于先 FLATTEN 再聚合的方案，同时天然保留 fields 为空的行，不会过滤数据。

核心SQL代码 #

SELECT
  file:id::VARCHAR AS ID,
  TRANSFORM(file:fields, element => element:value::VARCHAR) AS "VALUES"
FROM variant_table;

方案说明 #

• TRANSFORM 会遍历 file:fields 数组的每个元素，依次取每个元素的 value 字段转成字符串，最终直接返回转换后的数组，和需求的输出格式完全匹配
• 如果某行的 fields 是空数组，该查询会返回空数组 [] 作为 VALUES 字段值，不会跳过该行

如果需要兼容更老的Snowflake版本，也可以用先左连接展开再聚合的写法，性能略低于上面的方案：

SELECT
  t.file:id::VARCHAR AS ID,
  ARRAY_AGG(s.value:value::VARCHAR) AS "VALUES"
FROM variant_table t,
LATERAL FLATTEN(INPUT => t.file:fields, OUTER => TRUE) s
GROUP BY t.file:id;

原写法问题说明 #

你之前的写法默认是 INNER JOIN 方式做 FLATTEN，当 fields 为空时没有匹配的展开行，所以会直接过滤掉对应行；且没有做分组聚合，所以每个 value 单独占一行。

内容的提问来源于stack exchange，提问作者milos.blagojevic