How does array_contains work in Snowpark Python API

Question

I have the following DataFrame

----------
|"ARR"   |
----------
|[       |
|  "A",  |
|  "B",  |
|  "C"   |
|]       |
----------

Not I want to check whether a specific element is in ARR:

This gives a Error (failed to execute query...):

df\
    .withColumn("contains_A",array_contains("A",col("ARR")))\
    .show()

same here

df\
    .withColumn("contains_A",array_contains(lit("A"),col("ARR")))\
    .show()

and this gives FALSE

df\
    .withColumn("contains_A",array_contains(array_construct(lit("A")),col("ARR")))\
    .show()

How to do it?

user_95014 · Answer

The value you search for in ARRAY_CONTAINS must be a variant

with t0(arr) as (
    select parse_json('["a","b","c"]')
)
select array_contains('a'::variant, arr) CONTAINS_A
from t0;

CONTAINS_A
True

In Snowpark, I believe this should give you what you want: df.withColumn("contains_A",sql_expr("array_contains('A'::variant,ARR)")).show()