BigQuery REPEATED field contains

1
votes

Is there a way to check if a BigQuery REPEATED field contains a specific value?

I'm trying to do something like:

SELECT CASE WHEN column1 CONTAINS ("Test Value") THEN 1 ELSE 0 END FROM testdata

when column1 is a field of type STRING of mode REPEATED. I have multiple REPEATED columns of the same nature so having to flatten them all out seems tedious.

2
google-bigquery

2 Answers

5
votes

Try below 

SELECT 
  id_or_whatever_key_column_you_have, 
  SUM(CASE 
        WHEN column1 CONTAINS ("Test Value") THEN 1 
        ELSE 0 END
     ) WITHIN RECORD AS matches
FROM testdata

I realised - while copy/paste from your example I left some extra that can be removed without losing result - so it not only tells you if you have matches or not (true/false) but rather gives you count of matches within each record: 

SELECT 
  id_or_whatever_key_column_you_have, 
  SUM(column1 CONTAINS ("Test Value")) WITHIN RECORD AS matches
FROM testdata

I think Danny's answer has triggered this in my mind :o)

4
votes

You can use SOME to accomplish this, e.g.

SELECT SOME(column1 CONTAINS ("Test Value")) WITHIN RECORD FROM testdata