Snowflake中如何查询JSON数组内满足多条件的嵌套数据？

要筛选出同时存在名为joe且年龄超过14岁的学生，以及名为erica且年龄小于14岁的学生的班级，可通过以下两种方式实现：

方法1：使用FLATTEN展开数组后聚合判断 #

假设表名为class_students，class为字符串字段，students为VARIANT类型的JSON数组字段，查询语句如下：

SELECT class
FROM class_students,
LATERAL FLATTEN(input => students) AS s
GROUP BY class
HAVING 
    SUM(CASE WHEN s.value:first_name::STRING = 'joe' AND s.value:age::INT > 14 THEN 1 ELSE 0 END) > 0
    AND SUM(CASE WHEN s.value:first_name::STRING = 'erica' AND s.value:age::INT < 14 THEN 1 ELSE 0 END) > 0;

• LATERAL FLATTEN将JSON数组展开为单条学生记录
• 按班级分组后，通过HAVING统计满足两个条件的学生数量，确保均大于0（即班级同时存在两类学生）

方法2：使用ARRAY_EXISTS直接检查数组元素 #

无需展开数组，利用Snowflake的数组函数直接判断数组内是否存在符合条件的元素：

SELECT class
FROM class_students
WHERE
    ARRAY_EXISTS(students, (s) => s:first_name::STRING = 'joe' AND s:age::INT > 14)
    AND ARRAY_EXISTS(students, (s) => s:first_name::STRING = 'erica' AND s:age::INT < 14);

• ARRAY_EXISTS遍历数组，验证是否存在满足指定条件的元素
• 两个条件通过AND连接，确保班级同时满足两个要求

以上两种方法均会返回示例数据中的history班级，符合预期需求。

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