CODEWITHSUNDEEP
sqlgoogle-cloud-platformgoogle-bigquery
abinop
abinop

Reputation: 3183

How can I filter a BigQuery dataset that contains array data?

I have a query that contains array data. What I need to do is perform queries that can filter the results of the following screen, like:

"bring me the data for player alex". This should bring 2 rows:

1. [email protected], 2021-01-13 09:32:55.113 UTC, alex, 4, [8,10]
2. [email protected], 2021-01-13 09:30:07.572 UTC, alex, 10, [13,14]

BigQuery dataset with array data

how can such query be structured?

Upvotes: 0

Views: 274

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173191

Yet another option

select  email, timestamp, 
  player,
  must_respond_in, 
  responses
from data t, t.player as player with offset
left join t.must_respond_in as must_respond_in with offset using(offset)
left join t.responses as responses with offset using(offset)
where player = 'alex'

if to apply to sample data in your question - output is

enter image description here

Upvotes: 1

Kyrylo Bulat
Kyrylo Bulat

Reputation: 800

Try the followin in standard SQL in BigQuery:

with data as (
  select    "[email protected]" as email, CURRENT_TIMESTAMP() as timestamp,
            ['player1', 'alex', 'p3'] as player, [4, 4, 4] as must_respond_in, ['2, 9', '8, 10', '7'] as responses
  UNION ALL
  select    "[email protected]" as email, CURRENT_TIMESTAMP() as timestamp,
            ['player1', 'alex', 'p3'] as player, [14, 10, 14] as must_respond_in, ['1, 16', '13, 14', '0, 0'] as responses
)
select  email, timestamp, player[offset(x)],
        must_respond_in[offset(x)], responses[offset(x)]
from data, unnest(data.player) as player_name with offset x where player_name = 'alex';

I used WITH OFFSET clause to select elements that I need. WITH clause is used to simulate your data.

Upvotes: 2

Related Questions