What is <ARRAY> string and how do I filter it?

0
votes

Trying to query froma table in bigquery and filter using a column which is identified as TYPE: STRING, MODE: REPEATED.

When I try the following code:

select *
from transactions
where brands in (select brand from brand_list)

I get the following error:

Cannot execute IN subquery with uncomparable types ARRAY<STRING> and STRING

Why does this happen and how can I filter a column that contains ARRAY?

1
sqlgoogle-bigquery

1 Answers

2
votes

I'm guessing that you want something like this:

select *
from transactions
where exists (
  select 1 from brand_list, unnest(brands) as other_brand
  where brand = other_brand
)

This returns all rows where there is a brand in the array that also appears in the brand_list table.