Snowflake SQL如何展开数组列将数组元素拆分为单独行

你写的SQL中，FLATTEN函数内部嵌套了独立的全表查询子句，这个子查询会返回整张表所有行的currencies_added数组，而非和外层主表当前行关联的单个数组值，不符合单值子查询的返回要求，因此触发Single-row subquery returns more than one row报错。

侧展数组时，FLATTEN的入参直接引用主表当前行的数组列即可，不需要在函数内重复查询整张表，参考写法：

SELECT 
    t.*,
    f.value AS currencies_added_single
FROM FISH_OF_FORTUNE_DEV.DWH.FACT_DAILY_REVENUE t,
    TABLE(FLATTEN(input => t.currencies_added)) f;

• 展开后原表每行的数组有多少个元素，就会对应生成多少行数据，完全匹配你每个数组元素对应独立行的需求
• 字段f.value对应数组里的单个元素，针对你给出的示例数据[{"Gems": 24000},{"Gems": 1250}]，展开后会生成两行，分别对应两个Gems对象
• 如果需要提取对象内的字段值，直接用半结构化数据取值语法即可，比如取Gems数值可以写f.value:Gems::NUMBER AS gems_num

如果表中存在currencies_added为NULL、空数组的行，不想展开时丢失这部分原表数据，可以把隐式交叉连接改成左连接，写法如下：

SELECT 
    t.*,
    f.value AS currencies_added_single
FROM FISH_OF_FORTUNE_DEV.DWH.FACT_DAILY_REVENUE t
LEFT JOIN TABLE(FLATTEN(input => t.currencies_added)) f
  ON 1=1;

针对你提到的单数组最多100个元素的场景，FLATTEN函数的处理性能完全可以满足，不需要额外做拆分逻辑优化。

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