Snowflake ARRAY_CONTAINS: Searching Values in Arrays
The ARRAY_CONTAINS
function is checking whether a specific value is present in an array.
Syntax
The basic syntax for the ARRAY_CONTAINS
function is:
_10ARRAY_CONTAINS(<value_expr>, <array>)
where:
<value_expr>
: This is the value you are searching for within the array. It should be of a type that is comparable to the elements within the array.<array>
: This is the array within which the search is conducted.
The function returns a BOOLEAN
: TRUE
if the specified value is found in the array, FALSE
otherwise.
Usage notes
ARRAY_CONTAINS
does not support wildcards in thevalue_expr
.- To utilize wildcards, you could convert the array to a string using
ARRAY_TO_STRING
and then use SQL pattern matching functions likeLIKE
orREGEXP_LIKE
.
Examples
Search for a Value in an Array
Imagine we have a table named galactic_travel_guide that contains information about different planets in the galaxy. Each row represents a planet and includes an array of famous quotes or features that the planet is known for.
Now, suppose you’re compiling a guide for travelers who are particularly interested in planets known for “towels”. We can use ARRAY_CONTAINS
to find out which planets they should definitely plan to visit.
Input
Table: galactic_travel_guide
planet_name | notable_features |
---|---|
Earth | ['mostly relaxed', 'blue planet'] |
Vogon | ['poetry', 'construction bypasses'] |
Magrathea | ['luxury planets', 'deep thought'] |
Betelgeuse | ['trippy area', 'towels'] |
Snowflake SQL Query
_10SELECT planet_name, ARRAY_CONTAINS('towels'::VARIANT, notable_features) AS is_towel_famous_10FROM galactic_travel_guide;
Output
planet_name | notable_features |
---|---|
Earth | FALSE |
Vogon | FALSE |
Magrathea | FALSE |
Betelgeuse | TRUE |
![Y42 SQL completion user interface](/learn/_next/image?url=%2Flearn%2Fimages%2Fcta%2Fsql-completion.webp&w=3840&q=100)