PostgreSQL: 使用LIKE、ANY和通配符的Where子句

Question

我有一个包含字符串数组的表格（类型为character varying(255)[]）。

我想将给定的字符串与通配符（例如'query%'）进行比较，以便与该字段中的任何元素匹配。

以下请求可以正常工作并返回预期结果：

SELECT * FROM my_table WHERE 'query' ILIKE ANY(my_field)

但是使用通配符后，我没有得到任何结果：

SELECT * FROM my_table WHERE 'query%' ILIKE ANY(my_field)

我认为原因是通配符只支持在ILIKE运算符的右侧，但ANY(my_field)也必须在运算符之后。是否有一种方法可以实现我想要的结果？使用PostgreSQL 9.5。

- scandel

---

可能是PostgreSQL - text Array contains value similar to的重复问题。 - pozs

你说得对，这是一个重复的问题，我没有看到过。但我很高兴@klin写了一个详细的答案，在其他帖子中没有提到。 - scandel

Answer 1

您需要取消嵌套的数组字段：

with my_table(my_field) as (
values
    (array['query medium', 'large query']),
    (array['large query', 'small query'])
)
select t.* 
from my_table t,
lateral unnest(my_field) elem
where elem ilike 'query%';

            my_field            
--------------------------------
 {"query medium","large query"}
(1 row)

- klin

---

谢谢，这是完美的解决方案。我只需要添加一个DISTINCT（select distinct t.*），因为我的表中有一些行，在my_field中出现了多次“query％”。 - scandel

Answer 2

使用unnest()将数组转换为集合，并使用EXIST子句。

SELECT * FROM my_table t WHERE  EXISTS (SELECT unnest(t.my_field) AS f WHERE f ILIKE ‘query%’)

- Serg M Ten

---

谢谢，这也是一个不错的解决方案，因为所有东西都保留在where子句中，对于更复杂的请求可能会有用。但是您的请求引发了postgresql错误，我不得不修改它才能使其工作：SELECT * FROM my_table t WHERE EXISTS (SELECT f FROM unnest(t.my_field) AS f WHERE f ILIKE 'query%')。 - scandel