BigQuery展开嵌套STRUCT时自动为解嵌列添加前缀的实现方法

你遇到的问题是BigQuery使用*展开STRUCT类型字段时，默认保留子字段原名，导致字段来源无法区分、跨结构体同名字段冲突，以下是无需手动逐字段配置别名的实现方法：

元数据自动生成SQL法（通用生产可用） #

通过BigQuery内置的INFORMATION_SCHEMA元数据表，自动拼接带结构体前缀的SELECT语句，不管结构体下有多少字段都可以一次性生成，无需手动枚举：

1. 首先运行下面的SQL生成最终查询语句，记得将代码中的你的表名替换为实际查询的表名称：

SELECT
  FORMAT("SELECT %s FROM `knowndataset`",
    STRING_AGG(
      CASE
        WHEN column_name = 'settings' THEN (
          SELECT STRING_AGG(FORMAT("settings.%s AS settings_%s", REPLACE(field_path, 'settings.', ''), REPLACE(field_path, 'settings.', '')), ', ')
          FROM `knowndataset.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS`
          WHERE table_name = '你的表名' AND column_name = 'settings' AND field_path != 'settings'
        )
        WHEN column_name = 'tracking' THEN (
          SELECT STRING_AGG(FORMAT("tracking.%s AS tracking_%s", REPLACE(field_path, 'tracking.', ''), REPLACE(field_path, 'tracking.', '')), ', ')
          FROM `knowndataset.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS`
          WHERE table_name = '你的表名' AND column_name = 'tracking' AND field_path != 'tracking'
        )
        ELSE column_name
      END, ', '
    )
  ) AS final_query_sql
FROM `knowndataset.INFORMATION_SCHEMA.COLUMNS`
WHERE table_name = '你的表名'

2. 复制查询结果中final_query_sql字段的内容，直接执行即可得到预期结果：

• 原表非结构体字段（如示例中的id）保留原名
• settings下所有子字段自动添加settings_前缀，即settings_title、settings_values、settings_size
• tracking下所有子字段自动添加tracking_前缀，即tracking_website、tracking_views、tracking_id
• 自动处理所有重名字段，不会出现列名冲突

临时查询快捷方法 #

如果只是做临时数据探查、不需要留存正式SQL，可以直接在BigQuery控制台左侧的表结构面板中，按住Ctrl键（Mac系统按Command键）点击STRUCT字段前的展开箭头，控制台会自动在剪贴板中生成带结构体前缀的所有子字段带别名的代码，直接粘贴到查询编辑器中，再补充* except(settings, tracking)部分即可快速得到结果。

不建议继续使用无别名直接展开结构体.*的写法，除了遇到的重名、来源不清问题外，后续如果结构体新增字段，会直接导致下游依赖宽表的逻辑出现字段错位风险，带前缀的写法可以完全规避这个问题。

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