BigQuery中声明数组的数组报错，求正确实现方案

BigQuery不支持直接使用嵌套数组（ARRAY<ARRAY<STRING>>），这是你遇到第一个报错的核心原因；同时存储过程的变量类型与函数返回类型不匹配，引发了第二个报错。以下是两种可行的实现方式，优先推荐STRUCT方案：

方案一：使用STRUCT替代嵌套数组（最优方案） #

通过定义结构体数组，既能保留原有的数据分组结构，又完全符合BigQuery的类型规范。

修改后的函数代码 #

CREATE OR REPLACE FUNCTION MY_DATASET.MY_FUNCTION() 
RETURNS ARRAY<STRUCT<data STRING, param STRING>> AS (
  [
    STRUCT('DATA1' AS data, 'DATA1PARAM1' AS param),
    STRUCT('DATA2' AS data, 'DATA2PARAM2' AS param)
  ]
);

对应的存储过程代码 #

CREATE OR REPLACE PROCEDURE MY_DATASET.MY_PROCEDURE()
BEGIN
  -- 声明与函数返回类型一致的结构体数组变量
  DECLARE tablesArray ARRAY<STRUCT<data STRING, param STRING>>;
  SET tablesArray = MY_DATASET.MY_FUNCTION();
  
  -- 示例：遍历数组处理数据
  FOR item IN UNNEST(tablesArray) DO
    -- 替换为你的业务逻辑，比如执行查询、写入数据等
    SELECT item.data, item.param;
  END FOR;
END;

方案二：将内层数组转为字符串（兼容方案） #

如果业务场景不适合用STRUCT，可以把每组数据拼接成字符串，在存储过程中再拆分还原。

修改后的函数代码 #

CREATE OR REPLACE FUNCTION MY_DATASET.MY_FUNCTION() 
RETURNS ARRAY<STRING> AS (
  [
    'DATA1,DATA1PARAM1',
    'DATA2,DATA2PARAM2'
  ]
);

对应的存储过程代码 #

CREATE OR REPLACE PROCEDURE MY_DATASET.MY_PROCEDURE()
BEGIN
  DECLARE tablesArray ARRAY<STRING>;
  SET tablesArray = MY_DATASET.MY_FUNCTION();
  
  -- 遍历并拆分字符串获取原始数据
  FOR item IN UNNEST(tablesArray) DO
    DECLARE data STRING DEFAULT SPLIT(item, ',')[OFFSET(0)];
    DECLARE param STRING DEFAULT SPLIT(item, ',')[OFFSET(1)];
    -- 替换为你的业务逻辑
    SELECT data, param;
  END FOR;
END;

方案对比 #

• STRUCT方案：结构清晰，无需额外拆分操作，类型安全，是BigQuery中处理此类场景的最优选择。
• 字符串拼接方案：适合简单场景，但存在分隔符冲突风险（如果数据本身包含逗号），需要额外处理转义逻辑。

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