如何在BigQuery中提取嵌套JSON？自定义UDF异常排查

你的UDF在BigQuery中无法得到预期结果的核心问题是未将输入的字符串类型JSON解析为JavaScript对象，直接对字符串调用Object.keys()会遍历字符串的每个字符而非JSON的顶层键，导致后续逻辑完全偏离预期。此外还有变量未声明、类型匹配的细节问题，以下是修正后的完整方案：

修正后的JS临时UDF #

CREATE TEMP FUNCTION json2array(json STRING)
RETURNS ARRAY<STRUCT<
  id STRING, 
  tax INT64, 
  sales_quantity INT64, 
  sales_price INT64, 
  Discount INT64, 
  Quantity INT64
>>
LANGUAGE js AS """
  // 核心修复：将字符串JSON解析为JS对象
  const data = JSON.parse(json);
  const result = {};
  
  // 遍历JSON顶层键（Quantity、Discount、sales、tax）
  Object.keys(data).forEach(topKey => {
    const topValue = data[topKey];
    // 遍历每个顶层键下的id（id_1、id_2）
    Object.keys(topValue).forEach(idKey => {
      // 初始化当前id对应的结构对象
      if (!result[idKey]) {
        result[idKey] = {};
      }
      
      const idValue = topValue[idKey];
      // 处理嵌套对象（如sales下的price/quantity），拼接字段名
      if (typeof idValue === 'object' && idValue !== null) {
        Object.keys(idValue).forEach(nestedKey => {
          result[idKey][`${topKey}_${nestedKey}`] = idValue[nestedKey];
        });
      } else {
        // 非嵌套字段直接赋值
        result[idKey][topKey] = idValue;
      }
    });
  });
  
  // 将结果对象转换为数组，补充id字段后返回
  const finalResult = Object.entries(result).map(([id, values]) => {
    return { id, ...values };
  });
  
  return finalResult;
""";

关键修正说明 #

• JSON解析：新增JSON.parse(json)将输入的字符串转为可操作的JS对象，这是解决问题的核心。
• 变量规范：用const/let声明所有变量，避免隐式全局变量引发的意外问题。
• 类型匹配：将返回STRUCT的字段类型从STRING改为INT64，匹配原始数据的数字类型，避免不必要的类型转换。
• 逻辑简化：使用扩展运算符...values简化对象合并，去掉冗余的空值处理（原始数据无null场景）。

调用示例 #

假设你的表名为your_table，嵌套JSON字段名为nested_json，执行以下SQL即可得到目标结构：

SELECT 
  flattened.id,
  flattened.tax,
  flattened.sales_quantity,
  flattened.sales_price,
  flattened.Discount,
  flattened.Quantity
FROM your_table,
UNNEST(json2array(nested_json)) AS flattened

内容的提问来源于stack exchange，提问作者Kevin Adrian Halim