Query a subset of an array in snowflake

user_9840684 0
Viewed 4012

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!

snowflake-cloud-data-platform sql
3 Answers
user_11440350

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'));
user_1324380

The array_contains will let you answer your specific question, however I thought it might be useful to see how you can transform the array into something that looks more like a table.

The lateral flatten functions in snowflake are definitely worth taking a peak at if you're using arrays.

enter image description here

with cte as (
  select 'some other info_1' col_1 ,ARRAY_CONSTRUCT('cats','dogs') col_2  
union select 'some other info_2' col_1 ,ARRAY_CONSTRUCT('horses','cows','cats')
union select 'some other info_3' col_1 ,NULL
union select 'some other info_4' col_1 ,ARRAY_CONSTRUCT('dogs','fish')
union select 'some other info_5' col_1 ,ARRAY_CONSTRUCT('birds')
union select 'some other info_6' col_1 ,ARRAY_CONSTRUCT('cats')
union select 'some other info_7' col_1 ,NULL )

select col_1, animals.value from cte ,lateral flatten(col_2) animals 
where animals.value = 'cats';
user_13524900
Related