How to search/filter by unnest result?

user_118520 0
Viewed 379

I want to search/filter (case-insensitive) in the ARRAY(TEXT) (using PostgreSQL). While I found it was impossible directly, it was suggested that a solution with unnest could be used, so I started on that.

The table I'm using is the following:

from sqlalchemy.dialects.postgresql import ARRAY
 
 
class CaseStudy(db.Model):  # type: ignore
    __tablename__ = "case_studies"
    id = db.Column(db.Integer, primary_key=True)
    ...
    solutions = db.Column(ARRAY(db.Text))
    ...

I could do something similar with pure SQL (minus the lower-case part):
SELECT * FROM case_studies WHERE 'a' IN (select(unnest(case_studies.solutions)));

But I cannot convert it to SQLAlchemy. I think the closest I got was with
result = session.query(cls.id).filter('a' in func.unnest(cls.solutions))
(but I got NotImplementedError: Operator 'contains' is not supported on this expression)
or
result = session.query(cls.id).filter("a" in select(func.unnest(cls.solutions)))
but then I get TypeError: argument of type 'Select' is not iterable.

Any advice how to do this in SQLAlchemy? Also, how to convert unnest's results into lower-case?

sqlalchemy postgresql sql python
1 Answers

I was able to solve this with the following:

result = session.query(cls.id).select_from(func.unnest(cls.solutions).alias("sols")).filter(column("sols").ilike(f"%a%"))

If anyone posts a better solution, I'll re-accept the answer.

user_1185200
Related