Filtering on a highly nested STRUCT in BigQuery

Question

Let's say I have a row with a basic string array and a want to filter by an element in the array, I can use the pattern as below:

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'

How would I do the same thing with a struct within the top-level array, for example to get all items with members.hobbies = 'Guitar' in the below?

-- members.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;

My best attempt is the pretty hack-ish:

-- members.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'])]
) , 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'

What specifically do you want the output to be? You're getting the correct row, are you asking to only get the single array element instead of the whole array? In which case just select h instead of the structure? — MatBailie
– MatBailie, Commented Dec 22, 2022 at 2:15
@MatBailie maybe something like a more generalized pattern if the object is something like [{[{... I just find it very difficult to figure out how to traverse STRUCTs that contain an array in it (or an array that contains a struct in it). — David542
– David542, Commented Dec 22, 2022 at 2:16
I'm asking what you want the results to be and you're replying with a comment about generalised search. I'm now even less clear about your specific question. — MatBailie
– MatBailie, Commented Dec 22, 2022 at 2:18
If you have an object you can use table.column.object_element_name to access a specific element. If you have an array, you need to unnest it to translate to multiple rows. If the array elements are then objects, you can use the same syntax to access its elements, if the array elements are arrays, you can unnest them again. Ad infinitum. It's a bit clunky, but sql wasn't designed for nested structures, that's a bespoke retrofit by Google. — MatBailie
– MatBailie, Commented Dec 22, 2022 at 2:25
select * from teams where 'Guitar' IN UNNEST(members.hobbies); is not enough? — Jaytiger
– Jaytiger, Commented Dec 22, 2022 at 2:38

Jaytiger · Accepted Answer · 2022-12-22 03:46:05Z

2

Below query works but not recommended (use MatBailie's answer instead for your real dataset until having proper explanation)

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 * FROM teams WHERE 'Guitar' IN UNNEST(members.hobbies);

Query results

As MatBailie explains, ARRAY<STRUCT<ARRAY<STRING>>> should be unnested twice but dot notation like members.hobbies seems to work at IN UNNEST clause.

Looking forward for someone to give me a reasonable explanation for this syntax.

edited Dec 22, 2022 at 3:46

answered Dec 22, 2022 at 3:37

Jaytiger

12.3k2 gold badges7 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Jaytiger Over a year ago

Similar case - MIN(d IN UNNEST(concat_dates.dates)) works also in the answer here. stackoverflow.com/questions/74392207/…

MatBailie Over a year ago

Your link refers to arrays of arrays, not arrays of structs of arrays. Have you actually tested this?

Jaytiger Over a year ago

@MatBailie, As you know, bigquery doesn't support arrays of arrays. it's arrays of struct of arrays. ARRAY_AGG(STRUCT(dates)) OVER w0 AS concat_dates. If you see I'm missing something, I'd appreciate if you let me know. thanks..

MatBailie Over a year ago

I did not know that, and only noticed the question, not the answer, I will do some testing later today.

MatBailie · Accepted Answer · 2022-12-22 02:46:52Z

1

If your question is about tidying up your query, I would use...

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,       -- STRING
  t.members,    -- ARRAY of STRUCTS
  m,            -- STRUCT; each element of the ARRAY t.members
  m.name,       -- STRING; the name item from the STRUCT m
  m.hobbies,    -- ARRAY;  the hobbies item from the STRUCT m
  h             -- STRING; each element from the ARRAY m.hobbies
FROM
  teams   AS t
CROSS JOIN
  unnest(t.members) AS m
CROSS JOIN
  unnest(m.hobbies) AS h
WHERE
  h = 'Guitar'

The SELECT clause then includes everything at every level of aggregation, and you can pick and choose which you actually want.

edited Dec 22, 2022 at 2:46

answered Dec 22, 2022 at 2:39

MatBailie

87.5k19 gold badges112 silver badges144 bronze badges

1 Comment

David542 Over a year ago

thanks for this, I didn't know you could chain unnests, and so in your case doing teams t, unnest(t.members) m, unnest(m.hobbiest) h is a real big readability win -- thank you. Thank you.

Collectives™ on Stack Overflow

Filtering on a highly nested STRUCT in BigQuery

2 Answers 2

4 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related