如何在BigQuery SQL中将ARRAY<STRUCT>传递给JS UDF

原代码存在的问题 #

• arg2赋值逻辑错误：你声明的arg2是STRUCT数组类型，但原写法用SELECT AS STRUCT会返回单个STRUCT，同时查询返回的SchoolID为INT64类型，和你定义的STRUCT中col2 STRING类型不匹配
• SET语句语法错误：给变量赋值查询结果时，必须将完整的SELECT语句包裹在括号内
• res1赋值逻辑错误：UNNEST会将数组拆为多行结果，需要先聚合为数组才能匹配res1的ARRAY<STRING>类型

修正后完整代码 #

DECLARE arg1 ARRAY<STRING>;
DECLARE arg2 ARRAY<STRUCT <col1 STRING, col2 STRING> >;
DECLARE res1 ARRAY<STRING>;

SET arg1 = ARRAY<STRING>["Adams", "Joseph", "Davis", "Mary", "Jesus"] ;

CREATE TEMP FUNCTION  myfunction(arg1 ARRAY<STRING> , arg2 ARRAY<STRUCT<col1 STRING, col2 STRING> > ) 
RETURNS ARRAY<STRING>
LANGUAGE js AS '''
return ["a", "b", "c"]
''';

-- 改动点1：用ARRAY()构造STRUCT数组，同时将SchoolID转为STRING匹配类型定义
SET arg2 = ARRAY(
WITH TBL1 AS
 (SELECT 'Adams' as LastName, 50 as SchoolID UNION ALL
  SELECT 'Buchanan', 52 UNION ALL
  SELECT 'Coolidge', 52 UNION ALL
  SELECT 'Davis', 51 UNION ALL
  SELECT 'Eisenhower', 77)
SELECT STRUCT(LastName as col1, CAST(SchoolID AS STRING) as col2) FROM TBL1
);

-- 改动点2：将SELECT语句包入括号，用ARRAY()将UNNEST结果聚合为数组
SET res1 = ARRAY(SELECT res FROM UNNEST(myfunction( arg1, arg2 )) res);

-- 可执行此语句验证结果
-- SELECT res1;

核心语法说明 #

• BigQuery中可以用ARRAY(SELECT STRUCT(...) FROM ...)快速构造指定结构的数组
• 给变量赋值查询结果必须用SET 变量名 = (查询语句)的格式，不能省略外层括号
• 如果需要将多行结果存储为数组变量，需要用ARRAY()或ARRAY_AGG()做聚合

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