Is there a way to filter rows in BigQuery by the contents of an array?

I have data in a BigQuery table that looks like this:

[
    { "id": 1, "labels": [{"key": "a", "value": 1}, {"key": "b", "value": 2}] },
    { "id": 2, "labels": [{"key": "a", "value": 1}, {"key": "b", "value": 3}] },
    // a lot more rows
]

My question is, how can I find all rows where "key" = "a", "value" = 1, but also "key" = "b" and "value" = 3?

I’ve tried various forms of using UNNEST but I haven’t been able to get it right. The CROSS JOIN leaves me with one row for every object in the labels array, leaving me unable to query by both of them.

Answer

Try this:

select *
from mytable
where exists (select 1 from unnest(labels) where key = "a" and value=1)
  and exists (select 1 from unnest(labels) where key = "b" and value=3)

Advertisement

Answer