postgresql - Find rows where text array contains value similar to input

Question

I'm trying to get rows where a column of type text[] contains a value similar to some user input.

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);

I've got good a result using the unnest() function in a subquery but I need to query this in WHERE clause if possible.

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 ...

Is there any solution to this without using unnest() and if it is possible in WHERE clause?

Answer 1

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:

• How to use ANY instead of IN in a WHERE clause with Rails?

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.

Your attempt:

select * from someTable where '%someInput%' LIKE ANY(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).

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.

Related:

• Is there a way to usefully index a text column containing regex patterns?
• Can PostgreSQL index array columns?

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%'
  );

You may want to escape special character in someInput. See:

• Escape function for regular expression or LIKE patterns

Careful with the negation (NOT LIKE ALL (...)) when NULL can be involved:

• Check if NULL exists in Postgres array