如何使用LIKE函数筛选所有元素含指定后缀的数组行？

原问题问题点 #

你的原SQL逻辑搞反了：WHERE '%_123' LIKE ANY(bt.item) 是判断数组中存在至少一个元素匹配%_123（而且写法顺序错误，应该是元素 LIKE 匹配模式，而非模式 LIKE 元素），但你需要的是筛选出数组里所有元素都以'_123'为后缀的行，所以得调整判断逻辑。

几种可行解法 #

方法1：用ALL操作符检查所有元素 #

直接判断数组里的每一个元素都满足LIKE '%_123'，用ALL实现：

with basket_tbl as (
select 1 as id, array['orange_123', 'apple', 'grape'] as item union
select 2 as id, array['guava', 'apple_123', 'durian'] as item union
select 3 as id, array['strawberry_123', 'lime_123', 'leomon_123'] as item union
select 4 as id, array['mango_123', 'mangosteen', 'plum'] as item union
select 5 as id, array['plum', 'guava', 'peach'] as item
)
SELECT * 
FROM basket_tbl bt
WHERE ALL(SELECT unnest(bt.item) LIKE '%_123');

如果你的表可能存在空数组，可以额外加排除条件：AND bt.item @> array[]::text[]

方法2：拆分数组后分组校验 #

把数组拆成单行，按ID分组后用EVERY函数判断所有元素都符合条件：

with basket_tbl as (
select 1 as id, array['orange_123', 'apple', 'grape'] as item union
select 2 as id, array['guava', 'apple_123', 'durian'] as item union
select 3 as id, array['strawberry_123', 'lime_123', 'leomon_123'] as item union
select 4 as id, array['mango_123', 'mangosteen', 'plum'] as item union
select 5 as id, array['plum', 'guava', 'peach'] as item
)
SELECT id, item
FROM basket_tbl bt, unnest(bt.item) AS element
GROUP BY id, item
HAVING EVERY(element LIKE '%_123');

方法3：检查是否存在不符合条件的元素 #

用array_position查找数组中是否存在不符合后缀的元素，如果不存在就保留该行：

with basket_tbl as (
select 1 as id, array['orange_123', 'apple', 'grape'] as item union
select 2 as id, array['guava', 'apple_123', 'durian'] as item union
select 3 as id, array['strawberry_123', 'lime_123', 'leomon_123'] as item union
select 4 as id, array['mango_123', 'mangosteen', 'plum'] as item union
select 5 as id, array['plum', 'guava', 'peach'] as item
)
SELECT * 
FROM basket_tbl bt
WHERE array_position(bt.item, (SELECT elem FROM unnest(bt.item) elem WHERE elem NOT LIKE '%_123')) IS NULL;

结果验证 #

以上方法都会返回你预期的结果：

3   {strawberry_123,lime_123,leomon_123}

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