Snowflake SQL - How can I query with an array variable for an IN clause

user_378171 0
Viewed 1831

How can I set a variable as an array of values, that would be used in an IN clause?

This works:

SELECT *
  FROM TableA
 WHERE Col1 IN ('Value1', 'Value2', 'Value3');

But I can't figure out how to create a variable from that manually specified array to use. I want to do something like this:

SET values = ('Value1', 'Value2', 'Value3');

SELECT *
  FROM TableA
 WHERE Col1 IN $values;

The reported error: SQL compilation error: Number of variables on left-hand side of assignment (1) is not equal to the number of values on right-hand side (3)

So how can I create an arary variable for this purpose?

snowflake-cloud-data-platform
2 Answers

IN could be used. The prerequisite is transforming "list" variable to rows first.

-- sample table
CREATE OR REPLACE TABLE t AS SELECT $1 AS col FROM VALUES ('a'),('b'),('c'),('d');

Case 1: Using JSON and FLATTEN:

SET lst = '["a","b","e"]';

SELECT value::STRING FROM TABLE(FLATTEN(input=>parse_json($lst)));
/*
a
b
e
*/

SELECT * 
FROM t
WHERE col IN (SELECT value::STRING FROM TABLE(FLATTEN(input=>parse_json($lst))));
/*
a
b
*/

Case 2: Using string and SPLIT_TO_TABLE:

SET lst2 = 'a,b,e';

SELECT VALUE FROM TABLE(SPLIT_TO_TABLE($lst2, ','));
/*
a
b
e
*/

SELECT *
FROM t
WHERE col IN (SELECT TRIM(VALUE) FROM TABLE(SPLIT_TO_TABLE($lst2, ',')));
/*
a
b
*/
user_50708790

The ARRAY_CONTAINS( ) function can be your friend in a use case like this, example as follows:

create or replace temporary table TableA (Col1 varchar(100));
insert into TableA values ('hello'), ('stuff'), ('Value1'), ('Value2'), ('goodbye');

SET search_4me = '["Value1","Value2","Value3"]';

select *
from   TableA
where  array_contains(Col1::variant, parse_json($search_4me));

^^^ two rows returned Value1 and Value2

user_87675700
Related