如何在AWS Athena中查询Parquet格式表的结构体数组列符合多键值条件的记录

• 核心逻辑：分别判断labels数组中是否同时存在两个符合要求的结构体元素，两个条件取交集即可筛选出目标记录。

1. Spark SQL / 新版本Hive 通用写法 #

SELECT *
FROM reports
WHERE 
  -- 判断是否存在key=name且value=cat的元素
  EXISTS (
    SELECT 1 
    FROM UNNEST(labels) AS label 
    WHERE label.key = 'name' AND label.value = 'cat'
  )
  -- 判断是否存在key=owner且value=meow的元素
  AND EXISTS (
    SELECT 1 
    FROM UNNEST(labels) AS label 
    WHERE label.key = 'owner' AND label.value = 'meow'
  );

老版本Hive（不支持UNNEST语法）可用LATERAL VIEW写法实现：

SELECT DISTINCT r.*
FROM reports r
LATERAL VIEW EXPLODE(labels) l1 AS label1
LATERAL VIEW EXPLODE(labels) l2 AS label2
WHERE 
  label1.key = 'name' AND label1.value = 'cat'
  AND label2.key = 'owner' AND label2.value = 'meow';

2. Presto / Trino 简化写法 #

利用内置的ARRAY_EXISTS函数无需展开数组，执行效率更高：

SELECT *
FROM reports
WHERE 
  ARRAY_EXISTS(labels, x -> x.key = 'name' AND x.value = 'cat')
  AND ARRAY_EXISTS(labels, x -> x.key = 'owner' AND x.value = 'meow');

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