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 the value_expr.
To utilize wildcards, you could convert the array to a string using ARRAY_TO_STRING and then use SQL pattern matching functions like LIKE or REGEXP_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

Your AI copilot for Snowflake SQL

Write production-ready SQL with AI-based suggestions.

Get Started