Snowflake中如何判断值是否在数组列并生成布尔列？

核心问题在于Snowflake的ARRAY_CONTAINS函数是严格类型匹配的——即使值的字面量相同，不同类型的VARIANT（比如数字型vs字符串型）会被判定为不相等，这也是你示例中one_as_variant_is_in_array返回FALSE的原因。

针对「NUMBER列的值是否属于ARRAY列元素」的需求，分两种场景给出最佳方案：

场景1：数组元素为字符串类型（如示例中的['2','1']） #

此时需要将NUMBER列的值转换为与数组元素一致的字符串类型VARIANT，再传入ARRAY_CONTAINS：

WITH example_cte AS (
    SELECT
        1 AS num_value, -- 原始NUMBER列
        [ '2', '1' ] AS my_array
)
SELECT
    ARRAY_CONTAINS(TO_VARIANT(TO_VARCHAR(num_value)), my_array) AS num_is_in_array
    -- 结果为TRUE
FROM example_cte;

或者，也可以将数组的所有元素转换为数字类型后再匹配（注意：如果数组中存在非数字元素，该方法会报错，可改用TRY_TO_NUMBER容错）：

WITH example_cte AS (
    SELECT
        1 AS num_value,
        [ '2', '1' ] AS my_array
)
SELECT
    ARRAY_CONTAINS(TO_VARIANT(num_value), ARRAY_TRANSFORM(my_array, x => TRY_TO_NUMBER(x))) AS num_is_in_array
    -- 结果为TRUE
FROM example_cte;

场景2：数组元素为数字类型VARIANT #

如果数组本身存储的是数字类型的VARIANT（比如[1,2]），直接将NUMBER列转为VARIANT即可匹配：

WITH example_cte AS (
    SELECT
        1 AS num_value,
        [ 1, 2 ] AS my_array -- 数字类型数组
)
SELECT
    ARRAY_CONTAINS(TO_VARIANT(num_value), my_array) AS num_is_in_array
    -- 结果为TRUE
FROM example_cte;

关键注意事项 #

• 优先选择统一两边类型的方式，避免类型不匹配导致的判断错误；
• 如果数组元素类型不统一（同时存在数字和字符串），建议先清洗数据统一类型，再进行匹配；
• 使用ARRAY_TRANSFORM批量转换时，优先用带容错的转换函数（如TRY_TO_NUMBER、TRY_TO_VARCHAR）避免数据异常导致查询失败。

内容的提问来源于stack exchange，提问作者whoopscheckmate