Filter With Array Column With Postgres And Sqlalchemy

I have a simple table with an int[] column, and I'd like to be able to select rows where any one of their array elements matches a value I have, and I cannot figure out how to do t

Solution 1:

So you want to use the Postgres Array Comparator.

query = session.query(TestUser).filter(TestUser.numbers.contains([some_int])).all()

or

query = session.query(TestUser).filter(TestUser.numbers.any(25)).all()

Solution 2:

Damn SQLAlchemy, took me some time to figure out that the correct way will be:

TelegramUser.query.filter(TelegramUser.selected_exchanges.contains(f"{{{platform_name}}}")).all()

Where the platform_name is a str and the selected_exchanges column is defined as follows:

from sqlalchemy.dialects import postgresql as pg

selected_exchanges = db.Column(pg.ARRAY(db.String, dimensions=1), nullable=True)