如何创建BigQuery UDF以UNNEST多层REPEATED字段？

问题背景 #

我有一个包含多个REPEATED层级的BigQuery schema，尝试创建UDF简化查询：

• 单层REPEATED字段处理已实现，以下UDF可正常返回id数组：

CREATE TEMP FUNCTION struct_array_struct_scalar(data ANY TYPE) AS (
  (
    SELECT ARRAY_AGG(l1.element.id)
    FROM UNNEST(data.list) as l1
  )
);

SELECT struct_array_struct_scalar(my_field) as my_field_ids FROM my_table

• 多层嵌套REPEATED字段处理遇阻：目标数据路径为my_field.list[].element.rates.list[].element.rate.double，包含2个REPEATED层级（list[]），其余为嵌套记录。编写的UDF如下：

CREATE TEMP FUNCTION foo(data ANY TYPE) AS (
  (
    SELECT ARRAY_AGG(l2.element.rate.double)
    FROM UNNEST(
      (SELECT l1.element.rates.list
      FROM UNNEST(data.list) AS l1)
    ) AS l2
  )
);

执行时触发错误：

Scalar subquery produced more than one element

错误原因与修改方案 #

报错根源是内层子查询SELECT l1.element.rates.list FROM UNNEST(data.list) AS l1返回了多个数组（每个l1元素对应一个rates.list数组），但UNNEST()仅能接收单个数组，无法直接处理多数组集合。

需改用多层UNNEST关联写法，将两层REPEATED字段的展开放在同一个FROM子句中，通过逗号分隔（等价于CROSS JOIN UNNEST）遍历所有层级元素，再聚合目标字段：

修改后的UDF代码：

CREATE TEMP FUNCTION foo(data ANY TYPE) AS (
  (
    SELECT ARRAY_AGG(l2.element.rate.double)
    FROM UNNEST(data.list) AS l1,
         UNNEST(l1.element.rates.list) AS l2
  )
);

说明 #

该写法先展开第一层data.list得到所有l1元素，再对每个l1元素展开对应的rates.list得到l2元素，最终将所有l2中的rate.double值聚合为一个数组，完全匹配需求。

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