AWS Athena：如何利用contains()实现数组中子字符串查询

针对数组列中查找是否存在包含指定子字符串的元素，以下是不同数据库的具体实现方案：

PostgreSQL #

PostgreSQL原生支持数组类型，可通过以下两种高效方式实现子字符串匹配：

方法1：用unnest展开数组 + EXISTS子查询 #

将数组拆分为独立元素后，逐个检查是否包含目标子字符串：

SELECT *
FROM your_table
WHERE EXISTS (
    SELECT 1
    FROM unnest(array_col) AS element
    WHERE element LIKE '%目标子串%'
);

如果需要不区分大小写的匹配，把LIKE换成ILIKE即可。

方法2：用ANY操作符简化写法 #

利用PostgreSQL的数组表达式能力，直接判断子串是否匹配数组中的任意元素：

SELECT *
FROM your_table
WHERE '%目标子串%' LIKE ANY (array['%' || elem || '%' FOR elem IN unnest(array_col)]);

注意：避免直接用array_to_string(array_col, ',') LIKE '%目标子串%'，如果数组元素本身包含分隔符（比如逗号），会导致误匹配。

MySQL #

MySQL通常用JSON类型存储数组（8.0+版本推荐），或用逗号分隔字符串模拟数组，对应解法如下：

针对JSON数组列 #

用JSON_TABLE将JSON数组展开为行，再进行子串匹配：

SELECT DISTINCT t.*
FROM your_table t
JOIN JSON_TABLE(
    t.json_array_col,
    '$[*]' COLUMNS(elem VARCHAR(255) PATH '$')
) AS jt
WHERE jt.elem LIKE '%目标子串%';

针对逗号分隔的字符串（模拟数组） #

用正则表达式匹配，避免元素边界的误判：

SELECT *
FROM your_table
WHERE CONCAT(',', comma_separated_col, ',') REGEXP ',[^,]*目标子串[^,]*,';

性能优化提示 #

• PostgreSQL：若频繁进行此类查询，可给数组列创建GIN索引，或基于unnest后的元素创建表达式索引。
• MySQL：针对JSON数组，可给展开后的elem字段创建生成列索引，提升匹配速度。

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