BigQuery中半结构化数据查询排除指定嵌套列的方法

你原有的SQL写法没法实现需求——BigQuery的EXCEPT子句只能排除顶层列，不能直接指定嵌套层级的列路径。要排除object1.address.street下的value和id，得通过重构嵌套结构体来实现，给你两种实用写法：

简洁写法（仅修改目标嵌套层） #

如果object1.address下只有street这一个子结构，或者你只需要调整street的内容，用这种写法最省事：

SELECT
  *,
  -- 重构street结构体，排除value和id
  STRUCT(object1.address.street.* EXCEPT(value, id)) AS `object1.address.street`
FROM `project-id.dataset-id.table-id`

全面写法（保留其他嵌套列） #

如果object1或address下还有其他需要保留的子列，就逐层重构整个嵌套结构，避免误删其他内容：

SELECT
  *,
  STRUCT(
    STRUCT(
      -- 保留street下除value、id外的所有列
      object1.address.street.* EXCEPT(value, id) AS street,
      -- 保留address下除street外的其他列
      object1.address.* EXCEPT(street)
    ) AS address,
    -- 保留object1下除address外的其他列
    object1.* EXCEPT(address)
  ) AS object1
FROM `project-id.dataset-id.table-id`

关键说明 #

• * EXCEPT(列名)可以快速排除指定列，但只能作用于当前层级的字段，所以需要配合STRUCT来重构嵌套结构。
• 字段名带点的话要加反引号包裹，比如`object1.address.street`，避免语法报错。

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