BigQuery增删嵌套STRUCT字段无需重指定其余字段的方法

BigQuery 暂不支持你提到的SELECT * EXCEPT(pets[*].species_id)这种直接指定嵌套路径排除字段的语法，但可以通过数组子查询组合* EXCEPT()语法实现完全符合预期的效果，全程不需要手动枚举任何业务字段。

可直接运行的实现SQL #

WITH
Person AS (
  SELECT 'Alice' AS name, 10 AS address_id,
         [STRUCT("Rexi" AS name, 5 AS species_id), STRUCT("Luna", 6)] AS pets UNION ALL
  SELECT 'Bob', 11, [STRUCT("Ralfie", 5)] UNION ALL
  SELECT 'Chandra', 10, []),
Address AS (
  SELECT 10 AS id, 'Jump Street' AS street, 'UK' AS country UNION ALL
  SELECT 11, 'Paper Street', 'US'
  ),
Species AS (
  SELECT 5 AS id, 'Dog' as name, 11 as lifespan UNION ALL
  SELECT 6, 'Cat', 15
)
SELECT 
  p.* EXCEPT (address_id, pets),
  -- 重构pets数组：关联物种信息，移除嵌套的species_id字段
  ARRAY(
    SELECT AS STRUCT 
      pet.* EXCEPT(species_id),
      (SELECT AS STRUCT s.* EXCEPT(id)) AS species
    FROM UNNEST(p.pets) pet
    LEFT JOIN Species s 
      ON pet.species_id = s.id
  ) AS pets,
  -- 原有地址关联逻辑保持不变
  (SELECT AS STRUCT a.* EXCEPT(id)) AS address
FROM Person p
JOIN Address a 
  ON p.address_id = a.id

实现逻辑说明 #

• 顶层查询先通过p.* EXCEPT(address_id, pets)排除原有的address_id和原始pets字段，避免后续构造新字段时出现重名冲突
• 用ARRAY()子查询重新构造符合要求的pets数组：通过UNNEST(p.pets)把数组里的每个宠物STRUCT拆成单行记录，关联逻辑和顶层关联Address表完全一致：
  • 单个宠物记录用pet.* EXCEPT(species_id)自动排除不需要的species_id字段，保留宠物所有其他原有字段
  • 关联Species表后，复用地址处理的写法：(SELECT AS STRUCT s.* EXCEPT(id))把物种信息封装为嵌套的species STRUCT，自动排除物种表的id字段，不需要手动枚举name、lifespan等业务字段
• 空pets数组场景（比如示例中Chandra的记录）会自动保留空数组，不会出现字段缺失或NULL值
• 最终返回结构完全匹配你给出的预期JSON格式，后续如果Person、Species表新增字段，查询会自动带出，不需要额外修改SQL。

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