How do we filter struct datatypes using SQL?

Question

I am using mode analytics which has a struct datatype in the table.

The sample values available are as below. I have a case number and want to filter by that number to get all corresponding rows.

Answer 1

Use the following SELECT statement in BigQuery in standard SQL:

WITH data as (
 select 'Mobile' as channel, struct(struct(123 as accountid, 'afdw' as caseid, 124 as casenumber) as `case`, null as message, 'field' as status, null as gateway) as context
 union all
 select 'Mobile' as channel, struct(struct(123 as accountid, 'afdw' as caseid, 125 as casenumber) as `case`, null as message, 'field' as status, null as gateway) as context
)
select * from data where context.case.casenumber = 125;

WITH clause is used to simulate the data in your table