BigQuery嵌套数组字段重命名报错，如何修复查询？

表结构 #

原查询与报错 #

原查询尝试创建新表并将payload.plan.legs.shipmentLinks.id重命名为shipmentLinkId：

CREATE TABLE `project.dataset.table_renamed`
AS
SELECT 
  event_id, 
  payload.version,
  payload.plan.legs.id,
  payload.plan.legs.shipmentLinks.id AS shipmentLinkId,
  payload.plan.legs.shipmentLinks.source
FROM `project.dataset.table_name`

执行后报错：

Cannot access field id on a value with type ARRAY<STRUCT<id STRING, shipmentLinks STRUCT<id STRING, source STRING>>> at

修复方案 #

报错原因是legs和shipmentLinks都是REPEATED类型（即数组），BigQuery不允许直接访问数组内的结构体字段，必须先通过UNNEST处理数组，再操作内部字段。根据需求不同有两种修复方式：

方式1：保留嵌套数组结构 #

如果希望新表和原表结构一致，仅修改字段名，需要用ARRAY结合SELECT AS STRUCT重构嵌套数组：

CREATE TABLE `project.dataset.table_renamed`
AS
SELECT 
  event_id,
  payload.version,
  -- 重构legs数组
  ARRAY(
    SELECT AS STRUCT
      leg.id,
      -- 重构shipmentLinks数组，将id重命名为shipmentLinkId
      ARRAY(
        SELECT AS STRUCT
          sl.id AS shipmentLinkId,
          sl.source
        FROM UNNEST(leg.shipmentLinks) sl
      ) AS shipmentLinks
    FROM UNNEST(payload.plan.legs) leg
  ) AS legs
FROM `project.dataset.table_name`

方式2：展开为扁平表 #

如果不需要保留嵌套结构，想把数组层级展开为单条记录，可以用两次UNNEST：

CREATE TABLE `project.dataset.table_renamed`
AS
SELECT 
  event_id,
  payload.version,
  leg.id AS leg_id,
  sl.id AS shipmentLinkId,
  sl.source
FROM `project.dataset.table_name`,
UNNEST(payload.plan.legs) leg,
UNNEST(leg.shipmentLinks) sl

两种方案区别：

• 方式1保留原嵌套数组结构，适合需要维持数据层级的场景
• 方式2将每个leg和shipmentLink展开为独立行，适合扁平化分析的需求

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