如何在BigQuery中对嵌套STRUCT进行过滤查询？

基础场景回顾 #

当需要过滤包含基础字符串数组的数据时，我们可以通过UNNEST展开数组后匹配，示例如下：

with members as (
  select 'Bob' AS name, ['Running', 'Piano'] AS hobbies union all
  select 'Derek', ['Cooking']
) select name, hobbies from members, unnest(hobbies) as hobby where hobby = 'Piano'

问题场景 #

如果顶层数组中包含STRUCT（每个STRUCT包含name字段和hobbies字符串数组），该如何实现类似的过滤逻辑？比如要从以下数据中筛选出成员爱好包含Guitar的所有团队条目：

with teams as (
  select 'Cubs' AS name, [STRUCT<name STRING, hobbies ARRAY<STRING>>('Derek', ['Guitar', 'Cooking'])] as members union all
  select 'Jaguars', [('Bob', ['Running', 'Piano']), ('Sammy', ['Sailing'])]
) 
select t.name, members, y from teams t, unnest(members) as y;

我目前的实现方式较为繁琐，需要额外的子查询：

with teams as (
  select 'Cubs' AS name, [STRUCT<name STRING, hobbies ARRAY<STRING>>('Derek', ['Guitar', 'Cooking'])] as members union all
  select 'Jaguars', [('Bob', ['Running', 'Piano']), ('Sammy', ['Sailing'])]
) , m1 as (select t.*, hobbies from teams as t, unnest(members) as m_unnest)

select m1.name, m1.members from m1, unnest(hobbies) as h where h='Guitar'

求更优的实现方式。

更优实现方案 #

方案1：多层UNNEST直接过滤（保留原团队完整数据） #

通过嵌套UNNEST直接在主查询中完成过滤，无需额外子查询，代码更简洁：

with teams as (
  select 'Cubs' AS name, [STRUCT<name STRING, hobbies ARRAY<STRING>>('Derek', ['Guitar', 'Cooking'])] as members union all
  select 'Jaguars', [('Bob', ['Running', 'Piano']), ('Sammy', ['Sailing'])]
)
select distinct t.name, t.members
from teams t
cross join unnest(t.members) as team_member
cross join unnest(team_member.hobbies) as hobby
where hobby = 'Guitar';

• 核心逻辑：先展开顶层members数组得到单个STRUCT，再展开STRUCT内的hobbies数组进行匹配
• DISTINCT作用：避免同一团队因多个成员/多个匹配爱好重复返回

方案2：IN + UNNEST精准匹配（性能更优） #

如果只需要返回符合条件的团队及对应成员信息，可直接用IN结合UNNEST判断，无需展开爱好数组：

with teams as (
  select 'Cubs' AS name, [STRUCT<name STRING, hobbies ARRAY<STRING>>('Derek', ['Guitar', 'Cooking'])] as members union all
  select 'Jaguars', [('Bob', ['Running', 'Piano']), ('Sammy', ['Sailing'])]
)
select t.name, team_member
from teams t
cross join unnest(t.members) as team_member
where 'Guitar' in unnest(team_member.hobbies);

• 优势：减少一次数组展开操作，逻辑更直观，性能更高效，同时精准返回符合条件的成员数据

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