How to use ilike sqlalchemy on postgresql array field?

Question

I'm trying to match some names with the names I already have in my Postgresql database, using the following code:

last_like = '{last_name}%'.format(last_name)
matches = Session.query(MyTable).filter(or_(
    MyTable.name.ilike(last_like),
    MyTable.other_names.any(last_like, operator=ilike_op),
)).all()

Essentially it tries to match the name column or any of the other names stored as an array in the other_names column.

But I get:

KeyError: <function ilike_op at 0x7fb5269e2500>

What am I doing wrong?

Answer 1

For use postgresql array field you need to use unnest() function. But you can't use result of unnest() in where clause.

Instead, you can use array_to_string function. Searching on string of other_names will give the same effect

from sqlalchemy import func as F
last_like = "%qq%"
matches = session.query(MyTable).filter(or_(
    MyTable.name.ilike(last_like),
    F.array_to_string(MyTable.other_names, ',').ilike(last_like),
)).all()