I have table File with column file_names which is ARRAY type. W would like to build query searching records where any of array element is like "abc*" using wildcard.
I tried:
File.names.any("abc%", operator= operators.like_op).all()
but it doesn't work. It return sql:
SELECT file.id, file.names FROM file WHERE 'abc%' LIKE ANY(file.names)
but it return empty result. Do you have any suggestions, how to build such query?
The like operator applies to a text, not to an array, so you need to find a way to convert your array into text before applying the like operator. To do so in sql you can use array_to_string() function, see the manual.
For example :
select array_to_string('{"name_1", "name_2", "name_3"}' :: text[], ' ') LIKE '%name_2%' returns true.
The first character % is mandatory because the searched word may be at any place in the string which results from the array.
Then you can add the separator specified in the array_to_string() function before and after the searched word (LIKE '% name2 %') in order to exclude the results like somename_2.
For example :
select array_to_string('{"name_1", "name_2", "name_3"}' :: text[], ' ') LIKE '% name_2 %' returns true
but
select array_to_string('{"name_1", "somename_2", "name_3"}' :: text[], ' ') LIKE '% name_2 %' returns false.
Last but not least, for the seprator, you can specifiy a non printable character like chr(12) or a set of characters instead of the space character in order to secure the results of the query.
I found solution in SQL:
SELECT file.id, file.names FROM file WHERE EXISTS (SELECT 1 FROM unnest(file.names) AS n
WHERE n LIKE 'abc%')
and it works. But I don't know how to ask such query in sqlalchemy. Any ideas?