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

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?

CodePudding user response：

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

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