Skip to content
Advertisement

Filtering with exists in BigQuery

I’m running the following query in bigQuery and I don’t have the expected output. Isn’t that only the row with Alpha should be returned?

SELECT * FROM UNNEST([
  STRUCT(NULL AS a, '' AS b),
  (1, 'Alpha'),
  (2, 'Bravo'),
  (3, 'Charlie'),
  (4, 'Delta')
])
WHERE EXISTS (SELECT * FROM UNNEST([
  STRUCT(NULL AS a, '' AS b),
  (1, 'Alpha')
]))

Advertisement

Answer

Use below instead

SELECT * FROM UNNEST([
  STRUCT(NULL AS a, '' AS b),
  (1, 'Alpha'),
  (2, 'Bravo'),
  (3, 'Charlie'),
  (4, 'Delta')
])
WHERE (a,b) in UNNEST([
  STRUCT(NULL AS a, '' AS b),
  (1, 'Alpha')
])            

with output

enter image description here

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement