CODEWITHSUNDEEP
sqlgoogle-bigquery
loadbox
loadbox

Reputation: 656

How to check if a value exists in an array type column using SQL?

I am having a bigquery table with many columns including one array type column of zero or many integers.

For instance:

WITH sequences AS
  (SELECT [0, 1, 2, 3, 5] AS some_numbers
   UNION ALL SELECT [2, 4, 8, 16, 32] AS some_numbers
   UNION ALL SELECT [5, 10] AS some_numbers)
SELECT *
FROM sequences

+---------------------+
| some_numbers        |
+---------------------+
| [0, 1, 2, 3, 5]     |
| [2, 4, 8, 16, 32]   |
| [5, 10]             |
+---------------------+

Using BigQuery SQL, I want to generate another column exists if any value of tuple (3, 10) is included in some_numbers arrays.

My desired output:

+--------------------+--------+
| some_numbers       | exists |
+--------------------+--------+
| [0, 1, 2, 3, 5]    | True   |
| [2, 4, 8, 16, 32]  | False  |
| [5, 10]            | True   |
+--------------------+--------+

How can I do this?

Upvotes: 1

Views: 6482

Answers (2)

Guillaume
Guillaume

Reputation: 178

Mikhail's solution is nice but, in my humble opinion, the below query is cleaner:

select format('%T', some_numbers) some_numbers,
  EXISTS(select *
    from t.some_numbers number
    where number in (3, 10)
  ) as exist
from sequences t

Upvotes: 1

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173190

Consider below approach

select format('%T', some_numbers) some_numbers,
  (select count(1) > 0
    from t.some_numbers number
    where number in (3, 10)
  ) as exist
from sequences t

when applied to sample data in your question - output is

enter image description here

Note: I used format('%T', some_numbers) just for the sake of formatting output of array - but you might use just some_numbers instead

Upvotes: 3

Related Questions