如何在Snowflake中解析多行JSON表并展开数组元素

解决方案 #

你之前的写法报错是因为FLATTEN的输入是返回多行的子查询，FLATTEN单次仅支持处理单个数组/对象输入。适配多行JSON数组展开的正确写法如下：

SELECT
  t.value:a::NUMBER AS a,
  t.value:b::NUMBER AS b
FROM json_table jt,
LATERAL FLATTEN(input => jt.raw_data) t;

语法说明 #

• 使用LATERAL FLATTEN可以对源表的每一行单独执行展开操作，无需将所有源数据合并为单行输入，天然支持源表存在多行数据的场景
• 语法中的input => jt.raw_data指定每次展开的输入为当前行的raw_data字段，而非整张表的查询结果
• 末尾的::NUMBER是将JSON字段的VARIANT类型转换为数值类型，你可根据实际业务字段的类型调整为::STRING、::BOOLEAN等对应类型

执行上述SQL后即可得到你期望的输出结果。

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