BigQuery查询数组内STRUCT元素官方示例运行结果不一致问题

根因说明 #

你当前执行的SQL没有语法错误，运行结果和代码逻辑完全匹配。你对比的官方结果对应的是示例中对participant结构体字段做了显式展开的查询，和你运行的SQL不是同一段代码：

• 你当前的SELECT子句直接返回了整个participant结构体，因此输出第二列是包含name、splits两个子字段的复合结构
• 官方示例对应查询显式提取了结构体的子字段，所以输出会拆分出独立的name、splits列

修复方案 #

将SELECT部分调整为展开结构体字段的写法即可得到和官方一致的结果，修改后的SQL如下：

WITH races AS (
  SELECT "800M" AS race,
    [STRUCT("Rudisha" as name, [23.4, 26.3, 26.4, 26.1] as splits),
     STRUCT("Makhloufi" as name, [24.5, 25.4, 26.6, 26.1] as splits),
     STRUCT("Murphy" as name, [23.9, 26.0, 27.0, 26.0] as splits),
     STRUCT("Bosse" as name, [23.6, 26.2, 26.5, 27.1] as splits),
     STRUCT("Rotich" as name, [24.7, 25.6, 26.9, 26.4] as splits),
     STRUCT("Lewandowski" as name, [25.0, 25.7, 26.3, 27.2] as splits),
     STRUCT("Kipketer" as name, [23.2, 26.1, 27.3, 29.4] as splits),
     STRUCT("Berian" as name, [23.7, 26.1, 27.0, 29.3] as splits)]
       AS participants)
SELECT
  race,
  participant.name,
  participant.splits
FROM races r
CROSS JOIN UNNEST(r.participants) as participant;

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