BigQuery如何从ARRAY<STRUCT>类型字段中提取year作为独立列

问题原因 #

报错是因为customValues字段类型为ARRAY<STRUCT<year STRING, statCrewShirtNumber STRING>>，本质是由结构体组成的数组，无法直接通过字段名.结构体属性的语法访问内部属性，必须先处理数组结构再提取对应字段。

解决方案 #

场景1：将数组中所有结构体的year拆分为独立行（一行对应数组内的一个结构体元素） #

使用UNNEST打平数组后再提取属性即可，写法如下：

select 
    struct_item.year as year
from dataset.our_table,
unnest(customValues) as struct_item

如果需要保留customValues为空的原行，可以改用LEFT JOIN关联打平结果：

select 
    struct_item.year as year
from dataset.our_table
left join unnest(customValues) as struct_item on true

场景2：保留原行维度，将所有year提取为数组类型独立列 #

不需要拆分原行的情况下，可以直接通过数组映射提取所有year值：

select 
    array(select year from unnest(customValues)) as year_arr
from dataset.our_table

如果业务逻辑中customValues数组最多只有1个结构体元素，也可以直接取数组首位元素的属性，SAFE_OFFSET可避免数组为空时的索引越界报错：

select 
    customValues[SAFE_OFFSET(0)].year as year
from dataset.our_table

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