如何在BigQuery中实现接收任意STRUCT并生成指定JSON的UDF

方案一：改进版JavaScript UDF（支持未知STRUCT结构） #

针对你提到的JS UDF局限，可通过以下方式优化，实现接收任意STRUCT并保留类型信息：

CREATE OR REPLACE FUNCTION `your-project.your-dataset.struct_to_custom_json`(input ANY TYPE)
RETURNS STRING
LANGUAGE js AS """
// 映射JS类型到BigQuery原生类型
const typeMapper = {
  'number': 'INT64',
  'string': 'STRING',
  'object': (val) => val instanceof Date ? 'DATE' : 'STRUCT'
};

const output = [];
// 遍历STRUCT转为的对象键值对
for (const [key, rawValue] of Object.entries(input)) {
  let processedValue, fieldType;
  
  // 处理DATE类型：转换为YYYY-MM-DD格式的字符串
  if (rawValue instanceof Date) {
    processedValue = rawValue.toISOString().split('T')[0];
    fieldType = 'DATE';
  } else {
    processedValue = rawValue;
    fieldType = typeMapper[typeof rawValue] || 'UNKNOWN';
  }
  
  output.push({ key, value: processedValue, type: fieldType });
}

return JSON.stringify(output);
""";

-- 测试用例
SELECT `your-project.your-dataset.struct_to_custom_json`(
  STRUCT(1 AS num, "hi" as str, DATE "2014-01-01" as date)
);

输出结果：

[{"key":"num","value":1,"type":"INT64"},{"key":"str","value":"hi","type":"STRING"},{"key":"date","value":"2014-01-01","type":"DATE"}]

如果需要支持更多类型（如FLOAT64、BOOL），只需扩展typeMapper和对应的处理逻辑即可。

方案二：结合INFORMATION_SCHEMA的精准类型方案 #

若需要100%匹配BigQuery的原生字段类型，避免JS类型判断的误差，可借助表的元数据实现：

步骤1：获取STRUCT的字段元数据 #

假设你的STRUCT存储在your-project.your-dataset.target_table的target_struct字段中，先查询该STRUCT的字段信息：

SELECT 
  REPLACE(column_name, 'target_struct.', '') AS field_name,
  data_type
FROM `your-project.your-dataset.INFORMATION_SCHEMA.COLUMNS`
WHERE table_name = 'target_table' 
  AND column_name LIKE 'target_struct.%';

步骤2：动态生成转换SQL #

基于元数据生成动态SQL，将STRUCT转为指定格式的JSON：

DECLARE fields_template STRING;

-- 拼接字段处理模板
SET fields_template = (
  SELECT STRING_AGG(
    FORMAT(
      "STRUCT('%s' AS key, TO_JSON_STRING(target_struct.`%s`) AS value, '%s' AS type)",
      field_name, field_name, data_type
    ), ', '
  )
  FROM (
    SELECT 
      REPLACE(column_name, 'target_struct.', '') AS field_name,
      data_type
    FROM `your-project.your-dataset.INFORMATION_SCHEMA.COLUMNS`
    WHERE table_name = 'target_table' 
      AND column_name LIKE 'target_struct.%'
  )
);

-- 执行动态SQL生成结果
EXECUTE IMMEDIATE FORMAT("""
  SELECT TO_JSON_STRING(ARRAY_AGG(STRUCT_CONCAT(%s))) AS custom_json
  FROM `your-project.your-dataset.target_table`
""", fields_template);

封装为存储过程（复用性更强） #

如果需要多次使用，可封装成存储过程：

CREATE OR REPLACE PROCEDURE `your-project.your-dataset.struct_to_json_via_schema`(
  IN full_table_name STRING,
  IN struct_field_name STRING,
  OUT result_json STRING
)
BEGIN
  DECLARE schema_part STRING;
  DECLARE table_schema STRING;
  DECLARE table_name STRING;
  
  -- 拆分表名的schema和表部分
  SET table_schema = SPLIT(full_table_name, '.')[SAFE_OFFSET(0)];
  SET table_name = SPLIT(full_table_name, '.')[SAFE_OFFSET(1)];
  
  -- 生成字段处理模板
  SET schema_part = (
    SELECT STRING_AGG(
      FORMAT(
        "STRUCT('%s' AS key, TO_JSON_STRING(`%s`.`%s`) AS value, '%s' AS type)",
        REPLACE(column_name, struct_field_name || '.', ''),
        struct_field_name,
        REPLACE(column_name, struct_field_name || '.', ''),
        data_type
      ), ', '
    )
    FROM `your-project.INFORMATION_SCHEMA.COLUMNS`
    WHERE table_schema = table_schema
      AND table_name = table_name
      AND column_name LIKE struct_field_name || '.%'
  );
  
  -- 执行动态SQL并输出结果
  EXECUTE IMMEDIATE FORMAT("""
    SELECT TO_JSON_STRING(ARRAY_AGG(STRUCT_CONCAT(%s))) INTO result_json
    FROM `%s`
  """, schema_part, full_table_name);
END;

-- 调用示例
CALL `your-project.your-dataset.struct_to_json_via_schema`(
  'your-dataset.target_table', 'target_struct', @output
);
SELECT @output;

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