Snowflake中使用EXISTS与子查询报错：Unsupported subquery type cannot be evaluated

问题场景 #

表结构如下：

categories VARIANT DEFAULT NULL,
types VARIANT DEFAULT NULL,
id VARCHAR(255)

其中categories和types为包含{"name": "xxx", "id": "xxx"}结构的数组。需求是筛选出满足types.name='x'且categories.name='y'的所有id，原使用EXISTS子查询的写法报错：

Unsupported subquery type cannot be evaluated

（中文翻译：不支持的子查询类型无法评估）

解决方案 #

方案一：LATERAL JOIN展开数组筛选 #

通过LATERAL JOIN逐行展开数组，过滤匹配条件后去重，避免重复行：

SELECT DISTINCT t.id
FROM TEST.TEST.TEST t
LEFT JOIN LATERAL TABLE(FLATTEN(input => t.categories)) c
LEFT JOIN LATERAL TABLE(FLATTEN(input => t.types)) ty
WHERE c.value:name::string = 'y'
AND ty.value:name::string = 'x';

方案二：ARRAY_CONTAINS函数（高效推荐） #

无需展开数组，直接检查数组中是否存在符合条件的对象，性能更优：

SELECT id
FROM TEST.TEST.TEST
WHERE ARRAY_CONTAINS(OBJECT_CONSTRUCT('name', 'y')::VARIANT, categories)
AND ARRAY_CONTAINS(OBJECT_CONSTRUCT('name', 'x')::VARIANT, types);

报错原因 #

原EXISTS子查询写法报错是因为Snowflake对依赖外部行VARIANT字段的相关子查询支持有限，FLATTEN操作绑定到外部行字段时，优化器无法正确解析执行这类子查询。而LATERAL JOIN或ARRAY_CONTAINS是Snowflake官方推荐的处理数组字段条件筛选的方式。

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