How do you query an array in Standard SQL that meets a certain conditional?

Question

I am trying to pull records whose arrays only meet a certain condition.

For example, I want only the results that contain "IAB3".

Here is what the table looks like

Table Name:

bids

Column Names:

BidderBanner / WinCat

Entries:

1600402 / null
1911048 / null
1893069 / [IAB3-11, IAB3]
1214894 / IAB3

How I initially thought it would be

SELECT * FROM bids WHERE WinCat = "IAB3" 

but I get an error that says no match for operator types array, string.

The database is in Google Big Query.

Answer 1

Below is for BigQuery Standard SQL

#standardSQL
SELECT * FROM `project.dataset.bids` WHERE 'IAB3' IN UNNEST(WinCat)

You can test, play with above using sample data from your question as in example below

#standardSQL
WITH `project.dataset.bids` AS (
  SELECT 1600402 BidderBanner, NULL WinCat UNION ALL
  SELECT 1911048, NULL UNION ALL
  SELECT 1893069, ['IAB3-11', 'IAB3'] UNION ALL
  SELECT 1214894, ['IAB3'] 
)
SELECT * FROM `project.dataset.bids` WHERE 'IAB3' IN UNNEST(WinCat)  

with result

Answer 2

you need to use single quotes in sql for all strings. it should be WHERE WinCat = 'IAB3' not WHERE WinCat = "IAB3"

Answer 3

One method uses unnest(), something like this:

SELECT b.*
FROM bids b
WHERE 'IAB3' IN (SELECT unnest(b.WinCats))

However, array syntax varies among the databases that support them and they are no part of "standard SQL".

Answer 4

this will work:

SELECT * FROM bids WHERE REGEXP_LIKE (WinCat, '(.)*(IAB3)+()*');