查找文本数组包含类似于输入的值的行 [英] Find rows where text array contains value similar to input
问题描述
我正在尝试获取 text[] 类型的列包含类似于某些用户输入的值的行.
I'm trying to get rows where a column of type text[] contains a value similar to some user input.
到目前为止我所想和所做的是像这样使用 'ANY' 和 'LIKE' 运算符:
What I've thought and done so far is to use the 'ANY' and 'LIKE' operator like this:
select * from someTable where '%someInput%' LIKE ANY(someColum);
但它不起作用.查询返回与此查询相同的值:
But it doesn't work. The query returns the same values as that this query:
select * from someTable where 'someInput' = ANY(someColum);
我在子查询中使用 unnest() 函数得到了很好的结果，但如果可能，我需要在 WHERE 子句中查询.
I've got good a result using the unnest() function in a subquery but I need to query this in WHERE clause if possible.
为什么 LIKE 运算符不能与 ANY 运算符一起使用，而且我没有收到任何错误?我认为一个原因应该是 ANY 运算符在查询的右侧，但是 ...
Why doesn't the LIKE operator work with the ANY operator and I don't get any errors? I thought that one reason should be that ANY operator is in the right-hand of query, but ...
不使用 unnest() 是否有任何解决方案，如果可以在 WHERE 子句中使用?
Is there any solution to this without using unnest() and if it is possible in WHERE clause?
推荐答案
了解 ANY 是不是运算符但也很重要只能用于运算符的右侧的 SQL 构造.更多:
It's also important to understand that ANY is not an operator but an SQL construct that can only be used to the right of an operator. More:
LIKE 运算符 - 或更准确地说:表达式，重写为 ~~ 运算符在 Postgres 内部 - 期望 value 在左边，pattern 在右边.没有用于此的 COMMUTATOR运算符(就像简单的相等运算符 = 一样)所以 Postgres 不能翻转操作数.
The LIKE operator - or more precisely: expression, that is rewritten with to the ~~ operator in Postgres internally - expects the value to the left and the pattern to the right. There is no COMMUTATOR for this operator (like there is for the simple equality operator =) so Postgres cannot flip operands around.
您的尝试:
select * from someTable where '%someInput%' LIKE ANY(someColum);
已经翻转了左右操作数，所以 '%someInput%' 是 value 并且数组列 someColum 的元素被认为是模式(这不是您想要的).
has flipped left and right operand so '%someInput%' is the value and elements of the array column someColum are taken to be patterns (which is not what you want).
它将必须是 ANY(someColum) LIKE '%someInput%' - 除了 ANY 不可能 结构只允许在运算符的右侧.您在这里遇到了障碍.
It would have to be ANY(someColum) LIKE '%someInput%' - except that's not possible with the ANY construct which is only allowed to the right of an operator. You are hitting a road block here.
相关:
您可以标准化您的关系设计并将数组的元素保存在单独表中的单独行中.除此之外，unnest() 是解决方案，因为您已经找到了自己.但是，虽然您只对至少一个匹配元素的存在感兴趣，但 EXISTS 子查询将是最有效的，同时避免结果中的重复 - Postgres 可以在找到第一个匹配项后立即停止搜索:
You can normalize your relational design and save elements of the array in separate rows in a separate table. Barring that, unnest() is the solution, as you already found yourself. But while you are only interested in the existence of at least one matching element, an EXISTS subquery will be most efficient while avoiding duplicates in the result - Postgres can stop the search as soon as the first match is found:
SELECT *
FROM tbl
WHERE EXISTS (
SELECT -- can be empty
FROM unnest(someColum) elem
WHERE elem LIKE '%someInput%'
);
您可能想转义 someInput 中的特殊字符.见:
You may want to escape special character in someInput. See:
当涉及到 NULL 时，请注意否定(NOT LIKE ALL (...)):
Careful with the negation (NOT LIKE ALL (...)) when NULL can be involved:
这篇关于查找文本数组包含类似于输入的值的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
