Filter postgres STRING ARRAY Column with Flask-SQLAlchemy

Question

I need to filter a query result by a ARRAY of STRINGS Column, on Postgres database. And need it to be case insensitive, working as 'ilike' postgres function. Here is the query that I need to work in FLask-SQLAlchemy syntax:

select * from articles a 
where array_to_string(a.tags, ',') ilike any (array['%football%', '%basketball%'])

I've already tried these syntaxes:

Article.query.filter(Article.tags.contains(f"{{{tags}}}")).all()
Article.query.filter(Article.tags.ilike(f"%{tags}%")).all()
Article.query.filter(Article.tags.in_(tags).all()

Where tags is a list of strings like this:

tags = [
            'football', 
            'basketball', 
            'hockey', 
            'soccer', 
            'baseball', 
            'golf', 
            'fighting', 
            'tennis'
        ]

SQLAlchemy model definition:

class Article(db.Model):
    __tablename__ = 'articles'

    article_id = db.Column(db.BigInteger, primary_key=True, autoincrement=False)
    headline = db.Column(db.String)
    source = db.Column(db.String)
    summary = db.Column(db.String)
    tags = db.Column(ARRAY(db.String), default=[])
    timestamp = db.Column(db.DateTime)
    url = db.Column(db.String)
    .
    .
    .

I've already checked the documentation and another similar questions, and didn't find any solution that fits my needs.

I think you can use overlap in that case, check an example here: stackoverflow.com/questions/32747623/… — Jose Lora
– Jose Lora, Commented Nov 15, 2021 at 21:24
Works if I have a single word in each item of that tag column, sometimes I have like "Euro Football" and I just want to filter by "football", case insensitive, and need it to find the word inside a bigger string as: ilike ('%football%') — C. S. F. Junior
– C. S. F. Junior, Commented Nov 15, 2021 at 21:51
now I understand in that case maybe you can use column_property in your model and create a fake column from your array tags in your model to create the string from the array and then compare with contains, I will try to create the scenario but I need to prepare my environment. Check here to see what I'm talking about docs.sqlalchemy.org/en/14/orm/mapped_sql_expr.html — Jose Lora
– Jose Lora, Commented Nov 15, 2021 at 21:57

Jose Lora · Accepted Answer · 2021-11-18 22:10:44Z

1

To make like the query that are you talking about you can use the functions array_to_string and _any from SQLAlchemy functions for example like this:

from sqlalchemy import func

articles = Article.query.filter(
    func.array_to_string(Article.tags, ','). \
        ilike(func.any_(['%fighting%', '%golf%']))
    ).all()

Inside the any_ function you can pass down your array to make the search.

See any_ documentation sqlalchemy any_ column modifier.

edited Nov 18, 2021 at 22:10

answered Nov 18, 2021 at 21:23

Jose Lora

1,3884 gold badges14 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Filter postgres STRING ARRAY Column with Flask-SQLAlchemy

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related