BigQuery SQL 不同schema两表UNION时如何为表2补充空值嵌套字段

BigQuery 实现方案 #

要实现该需求，核心是在保留表2其余所有字段不变的前提下，修改嵌套结构体details.employment.locations下的指定字段值为NULL，具体分两种常见场景： #

场景1：details.employment.locations为单个结构体（非数组） #

直接通过嵌套STRUCT构造修改指定字段即可，其余字段原样保留：

SELECT
  * EXCEPT(details),
  -- 重构details结构体，保留其余层级原有字段
  STRUCT(
    (SELECT AS STRUCT details.employment.* EXCEPT(locations),
      -- 重构locations结构体，将指定字段设为NULL
      STRUCT(
        CAST(NULL AS STRING) AS city,
        CAST(NULL AS STRING) AS regionCode,
        CAST(NULL AS STRING) AS Formatted,
        -- 保留locations下除上述三个字段外的其余字段
        details.employment.locations.* EXCEPT(city, regionCode, Formatted)
      ) AS locations
    ) AS employment
    -- 若details下还有employment之外的字段，可在此处追加原样保留即可
  ) AS details
FROM `你的项目ID.你的数据集.表2`

场景2：details.employment.locations为结构体数组（存储多条location记录） #

需要结合ARRAY函数遍历数组元素逐个修改：

SELECT
  * EXCEPT(details),
  STRUCT(
    (SELECT AS STRUCT details.employment.* EXCEPT(locations),
      ARRAY(
        SELECT AS STRUCT
          CAST(NULL AS STRING) AS city,
          CAST(NULL AS STRING) AS regionCode,
          CAST(NULL AS STRING) AS Formatted,
          loc.* EXCEPT(city, regionCode, Formatted)
        FROM UNNEST(details.employment.locations) AS loc
      ) AS locations
    ) AS employment
  ) AS details
FROM `你的项目ID.你的数据集.表2`

UNION操作补充说明 #

处理后的表2结构就和表1完全对齐了，直接在上述查询结果后加UNION ALL（需要去重则用UNION DISTINCT）拼接表1的全量查询即可。

注意：CAST(NULL AS 字段类型)要和表1中对应字段的类型完全匹配，若对应字段不是STRING类型，自行修改CAST的目标类型即可。

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