在BigQuery中实现含Struct数组的表与普通表的Union并填充空值

场景示例 #

假设你有两张表：

• 表A（含嵌套数组）：

  CREATE TABLE `project.dataset.table_a` (
    id INT64,
    info ARRAY<STRUCT<name STRING, age INT64>>
  );
  

  数据示例：

  id	info
  1	[{"name":"Alice", "age":30}]
  2	[{"name":"Bob", "age":25}]

• 表B（普通表）：

  CREATE TABLE `project.dataset.table_b` (
    id INT64,
    name STRING,
    age INT64
  );
  

  数据示例：

  id	name	age
  3	Charlie	35
  4	Dave	40

错误原因 #

直接执行UNION ALL时，表B没有info列，若用NULL填充，BigQuery无法自动匹配ARRAY<STRUCT>类型，导致类型不兼容报错。

正确SQL语句 #

需要将表B的字段转换为与表A完全一致的结构，用空数组或类型匹配的空值填充嵌套列：

方式1：用空数组填充嵌套列 #

SELECT id, info
FROM `project.dataset.table_a`
UNION ALL
SELECT 
  id,
  [] AS info  -- 构造空的STRUCT数组，匹配表A的info类型
FROM `project.dataset.table_b`;

方式2：显式CAST指定类型（更严谨） #

如果需要明确指定空值的类型，避免隐式转换问题：

SELECT id, info
FROM `project.dataset.table_a`
UNION ALL
SELECT 
  id,
  CAST(NULL AS ARRAY<STRUCT<name STRING, age INT64>>) AS info
FROM `project.dataset.table_b`;

结果验证 #

执行后会得到统一结构的结果：

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