Athena中array<string>类型列的通配符搜索实现方法

问题复现 #

Athena数据表mytable含array<string>类型字段myarr，执行如下语句尝试筛选数组中存在以eart开头的元素（如earth）的记录时，无法返回预期结果：

select * from mytable
where contains(myarr,'eart%');

错误原因 #

Athena基于Presto/Trino引擎，内置的contains()数组函数仅支持元素精确匹配，不会将传入字符串中的%识别为通配符，上述语句实际只会匹配数组中恰好存在值为eart%的元素的记录，自然无法命中earth相关结果。

正确实现方案 #

根据匹配需求选择对应写法：

• 需求为精确匹配数组中存在值为earth的元素：直接去除通配符即可

  SELECT * FROM mytable
  WHERE contains(myarr, 'earth');
  

• 需求为通配符模糊匹配（如匹配所有以eart开头的数组元素，含earth、earthen、earthquake等），优先使用any_match()函数遍历判断，写法简洁性能更好：

  SELECT * FROM mytable
  WHERE any_match(myarr, elem -> elem LIKE 'eart%');
  

  该函数会逐行遍历数组内的每个元素，只要存在任意一个元素匹配LIKE规则，当前行就会被命中。如果需要不区分大小写匹配，将LIKE替换为ILIKE即可。
• 若使用的Athena引擎版本较老不支持any_match()，可以通过UNNEST将数组展开为行后做匹配，注意加DISTINCT避免同一行因多个元素匹配产生重复结果：

  SELECT DISTINCT m.* 
  FROM mytable m
  CROSS JOIN UNNEST(m.myarr) AS t(elem)
  WHERE t.elem LIKE 'eart%';
  

补充说明 #

数组模糊匹配的通配符规则和普通字符串LIKE规则完全一致：

• %代表匹配任意长度的任意字符
• _代表匹配单个任意字符
• 若需要匹配字符串中真实存在的%或_，需要在对应字符前加转义符声明。

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