Query a subset of an array in snowflake

Question

I am attempting to subset a SQL query in snowflake based on elements of an array in one of my columns, but not sure how to do that.

For instance if column2 were an array data type that looked like this

SELECT column2
FROM table
LIMIT 7;

with output:

Row column2
1 ["cats","dogs"]
2 ["horses","cows","cats"]
3 NULL
4 ["dogs","fish]
5 ["birds"]
6 ["cats"]
7 NULL

And I wanted to subset the data and run a query that pulled in any row that had "cats" as an element in any of the arrays in column 2 -so rows 1,2, and 6- how would I construct that query?

Using something like this doesn't work:

SELECT column1, column2, column3
FROM Table
WHERE column2 = "cats" (or using an IN statement)

and results in an error message as an invalid identifier "cats" which I would expect since it is in an array

any insight would be appreciated!

Answer 1

You want array_contains():

where array_contains('cats'::variant, column2)

Answer 2

ARRAY_CONTAINS() works, but you have to be careful with types.

For example, this one returns false:

select array_contains('2020-01-01'::date::variant
    , array_construct('2020-01-01', '2019-01-01'));

But these return true:

select array_contains('2020-01-01'::date::string::variant
    , array_construct('2020-01-01', '2019-01-01'));


select array_contains('2020-01-01'::date::variant
    , array_construct('2020-01-01'::date, '2019-01-01'));

In the case of strings, this one returns a compilation error (as you saw):

select array_contains('cats'
    , array_construct('cats', 'dogs'));

-- SQL compilation error: error line 1 at position 7 Invalid argument types for function 'ARRAY_CONTAINS': (VARCHAR(4), ARRAY)

But this one fixes it:

select array_contains('cats'::variant
    , array_construct('cats', 'dogs'));