BigQuery函数能否返回二维JSON数组？求实现方案

需求说明 #

需要将包含嵌套结构体数组的JSON数据转换为二维字符串数组，具体转换如下：

原始数据格式：

{"column1" : "test1",
  "column2" : "test2", 
  "column3": [{'field1': 'a', 'field2': 'b'}, {'field1': 'c', 'field2': 'd'}]
}

期望转换结果：

{"column1" : "test1",
  "column2" : "test2", 
  "column3": [['a', 'b'], ['c', 'd']]
}

问题 #

编写的JS函数无法正确声明返回类型为ARRAY<ARRAY<STRING>>，现有函数代码如下：

CREATE TEMP FUNCTION JsonToItems(items ARRAY<STRUCT<a STRING, b STRING>>)
RETURNS ARRAY<ARRAY<STRING>> (<--- 无法配置该返回类型)
LANGUAGE js AS r"""
  console.log(items);
  var arr_1 = [];
    for (i = 0; i < items.length; i++) {
        var arr_2 = [];
        var keys = Object.keys(items[i]);
        for (var j = 0; j < keys.length; j++) {
            arr_2.push(items[i][keys[j]]);
        }
        arr_1.push(arr_2);
    }
  
  return arr_1;
""";

解决思路及修正代码 #

BigQuery对JS函数的返回类型声明有严格语法要求，同时需要确保JS返回的数组结构能被正确映射到BigQuery的数组类型。以下是修正后的实现：

方法1：修正函数类型声明与实现 #

CREATE TEMP FUNCTION JsonToItems(items ARRAY<STRUCT<field1 STRING, field2 STRING>>)
RETURNS ARRAY<ARRAY<STRING>>
LANGUAGE js AS """
  const result = [];
  for (const item of items) {
    // 显式指定字段顺序提取值，避免Object.keys的顺序不确定性
    result.push([item.field1, item.field2]);
  }
  return result;
""";

-- 测试示例
WITH sample_data AS (
  SELECT 
    "test1" AS column1,
    "test2" AS column2,
    [STRUCT("a" AS field1, "b" AS field2), STRUCT("c" AS field1, "d" AS field2)] AS column3
)
SELECT 
  column1,
  column2,
  JsonToItems(column3) AS column3
FROM sample_data;

方法2：无需JS函数，纯SQL实现 #

如果不需要动态处理字段，也可以直接用SQL数组构造实现：

WITH sample_data AS (
  SELECT 
    "test1" AS column1,
    "test2" AS column2,
    [STRUCT("a" AS field1, "b" AS field2), STRUCT("c" AS field1, "d" AS field2)] AS column3
)
SELECT 
  column1,
  column2,
  ARRAY(SELECT [field1, field2] FROM UNNEST(column3)) AS column3
FROM sample_data;

关键说明 #

1. JS函数中避免依赖Object.keys的顺序，因为对象属性顺序在JS中不保证稳定，建议显式指定字段顺序提取值。
2. BigQuery的ARRAY<ARRAY<STRING>>类型声明语法是合法的，之前的问题可能是语法书写错误（比如多余的括号）或字段名不匹配（原函数参数结构体用a/b，而原始数据是field1/field2）。

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