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?

The solution without using `unnest` would be to properly normalize your data model. — , Jan 07 '16 at 14:33
I thought to create a new table instead of a column of this data type if there wasn't any other solution. I'll do it, so! Thanks for your answer! — Jose Hermosilla Rodrigo, Jan 07 '16 at 14:37

Erwin Brandstetter · Accepted Answer · 2023-03-09T23:30:47.033

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?

The LIKE operator - or more precisely: key word, 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.

Your attempt:

select * from someTable where '%someInput%' LIKE ANY(someColum);

... has left and right operand backwards. '%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 something like ANY (someColum) LIKE '%someInput%' - but the ANY construct is only allowed to the right of an operator. You are hitting a road block.

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 and avoid duplicates in the result. Postgres can stop the search as soon as the first match is found:

SELECT *
FROM   tbl
WHERE  EXISTS (
    SELECT -- SELECT list can be empty for this purpose
    FROM   unnest(someColum) elem
    WHERE  elem LIKE '%someInput%'
  );

You may want to escape special characters 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

Thank you for the info!! – Jose Hermosilla Rodrigo Jan 07 '16 at 15:10 — Jose Hermosilla Rodrigo, Jan 07 '16 at 15:10

score 10 · Answer 2 · answered Jun 12 '18 at 01:05

An admittedly imperfect possibility might be to use ARRAY_TO_STRING, then use LIKE against the result. For example:

SELECT *
FROM someTable
WHERE ARRAY_TO_STRING(someColum, '||') LIKE '%someInput%';

This approach is potentially problematic, though, because someone could search over two array elements if they discover the joining character sequence. For example, an array of {'Hi','Mom'}, connected with || would return a result if the user had entered i||M in place of someInput. Instead, the expectation would probably be that there would be no result in that case since neither Hi nor Mom individually contain the i||M sequence of characters.

Rotareti · Answer 3 · 2020-11-06T13:05:59.013

My question was marked duplicate and linked to a question out of context by a careless mod. This question comes closest to what I asked so I leave my answer here. (I think it may help people for who unnest() would be a solution)

In my case a combination of DISTINCT and unnest() was the solution:

SELECT DISTINCT ON (id_) *
FROM (
  SELECT unnest(tags) tag, *
  FROM someTable
  ) x
WHERE (tag like '%someInput%');

unnest(tags) expands the text array to a list of rows and DISTINCT ON (id_) removes the duplicates that result from the expansion, based on a unique id_ column.

Update

Another way to do this without DISTINCT within the WHERE clause would be:

SELECT *
FROM someTable 
WHERE (
  0 < (
    SELECT COUNT(*) 
    FROM unnest(tags) AS tag
    WHERE tag LIKE '%someInput%'
  )
);

score 0 · Answer 4 · answered May 14 '20 at 19:31

Please check this out.

This answer was exactly what I was looking for. It also provides for some useful tips (and examples) in case you need more flexibility.

It basically explains the ANY(), the @> and the && operators.

"If you want to search multiple values, you can use @> operator"

"@> means contains all the values in that array. If you want to search if the current array contains any values in another array, you can use &&"

Find rows where text array contains value similar to input

4 Answers4

Linked