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

Question

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?

Mikhail Berlyant · Accepted Answer · 2021-10-10 16:47:36Z

3

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

answered Oct 10, 2021 at 16:47

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Guillaume · Accepted Answer · 2022-07-07 11:43:50Z

1

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

answered Jul 7, 2022 at 11:43

Guillaume

1781 gold badge3 silver badges18 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related