Is value in array column in Snowflake?

I need to generate a boolean column that evaluates whether a value (e.g. 1) is in an array (e.g. [1,2]) for each row of my table. My question is: what is the best way to do this?

As I understand, all elements of an ARRAY in Snowflake are of type VARIANT. These demonstrate the options I'm working with. I'm personally trying to figure out how to see if the value in a NUMBER column is an element of the ARRAY column.

Example

WITH example_cte AS (
    SELECT
        CAST(1 AS VARIANT) one_as_variant,
        CAST('1' AS VARIANT) string_one_as_variant,
        CAST(CAST(1 AS VARCHAR) AS VARIANT) as one_as_varchar_then_variant,
        [ '2', '1' ] AS my_array
)
SELECT
    ARRAY_CONTAINS(one_as_variant, my_array) as one_as_variant_is_in_array, -- evaluates as FALSE
    ARRAY_CONTAINS(string_one_as_variant, my_array) as string_one_as_variant_is_in_array, -- evaluates as TRUE
    ARRAY_CONTAINS(one_as_varchar_then_variant, my_array) as one_as_varchar_then_variant_is_in_array -- evaluates as TRUE
FROM
    example_cte;

So the variant "have a type", and thus string are not number, and making an array of numbers, fails for the strings, but passes for the numbers:

SELECT
    1::VARIANT as v1_n,
    '1'::VARIANT as v1_s,
    1::VARCHAR::VARIANT as v1_n_s,
    array_construct('2', '1') as a1,
    array_construct(2, 1) as a2,
    ARRAY_CONTAINS(v1_n, a1) as a1_contains_v1_n,
    ARRAY_CONTAINS(v1_s, a1) as a1_contains_v1_s,
    ARRAY_CONTAINS(v1_n_s, a1) as a1_contains_v1_n_s,
    ARRAY_CONTAINS(v1_n, a2) as a2_contains_v1_n,
    ARRAY_CONTAINS(v1_s, a2) as a2_contains_v1_s,
    ARRAY_CONTAINS(v1_n_s, a2) as a2_contains_v1_n_s

so then if we ask what the types are:

SELECT
    1::VARIANT as v1_n,
    TYPEOF(v1_n) as t_v1_n,
    '1'::VARIANT as v1_s,
    TYPEOF(v1_s) as t_v1_s,
    1::VARCHAR::VARIANT as v1_n_s,
    TYPEOF(v1_n_s) as t_v1_n_s,
    array_construct('2', '1') as a1,
    TYPEOF(a1[0]) as t_a1_0,
    array_construct(2, 1) as a2,
    TYPEOF(a2[0]) as t_a2_0

so how to get around this?

If you array have mixed types, you will have to rebuild them, but then you can know what to cast your values to.

OR if you know the type of array is always strings, like in your example, you can just cast the values:

SELECT
    1::VARIANT as v1_n,
    '1'::VARIANT as v1_s,
    1::VARCHAR::VARIANT as v1_n_s,
    array_construct('2', '1') as a1,
    array_contains(v1_n::text::variant, a1) as c_v1_n,    
    array_contains(v1_s::text::variant, a1) as c_v1_s,   
    array_contains(v1_n_s::text::variant, a1) as c_v1_n_s