1

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.

J.Fratzke
  • 1,415
  • 15
  • 23

2 Answers2

5

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)

Mikhail Berlyant
  • 165,386
  • 8
  • 154
  • 230
4

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

SELECT SOME(column1 CONTAINS ("Test Value")) WITHIN RECORD FROM testdata
Danny Kitt
  • 3,241
  • 12
  • 22