CODEWITHSUNDEEP
google-bigquery
SOO
SOO

Reputation: 3

How to query multiple STRUCTs in BigQuery in wildcard-like way

I struggle to query multiple STRUCTs which share same record fields each other. Let me show you how the table looks like.

Tables with multiple STRUCTs with same record fields

Each mango, melon, apple, banana STRUCT(RECORD) share same fields-qty, price.

Now I want to query them at once like "Show me the qty > 5." Is ther any wildcard-like way to do this? Maybe something like SELECT %.qty >5. Of course It is an invalid way(just for an example).

I know that the best way is to change the schema like fruit, fruit.qty, fruit.price and put the mango and others to fruit filed data, not remain them as a field itself.

However for some reason, I want to keep that schema and query multiple RECORDs at once. Could It be possible?

Thank you.

Upvotes: 0

Views: 306

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

Consider below approach

with temp as (
  select 
    trim(fruit, '"') as fruit, 
    cast(json_extract(info, '$.qty') as int64) as qty,
    cast(json_extract(info, '$.price') as float64) as price
  from your_table t, 
  unnest(split(trim(to_json_string(t), '{}'), '},')) record,
  unnest([struct(
    split(record, ':{')[offset(0)] as fruit, 
   '{' || split(record, ':{')[offset(1)] || '}' as info)
  ])
)
select * 
from temp
where qty > 5

if applied to sample data like in your question - output is

enter image description here

Upvotes: 0

Related Questions