如何用Flask-SQLAlchemy对Postgres的STRING ARRAY列做不区分大小写过滤

首先从sqlalchemy引入内置的func工具，用于调用PostgreSQL原生函数：

from sqlalchemy import func

第一步先对传入的tags列表做预处理，给每个标签前后拼接模糊匹配符号%，适配ilike的语法要求：

tags = [
    'football', 
    'basketball', 
    'hockey', 
    'soccer', 
    'baseball', 
    'golf', 
    'fighting', 
    'tennis'
]
processed_tags = [f"%{tag}%" for tag in tags]

然后构造查询语句即可，生成的SQL和你期望的原生语句完全一致：

result = Article.query.filter(
    func.array_to_string(Article.tags, ',').ilike(func.any(processed_tags))
).all()

原写法失效原因说明 #

• Article.tags.contains是数组精确包含匹配，区分大小写，且不支持模糊匹配
• 直接对ARRAY类型调用.ilike()不符合语法规则，ilike仅支持字符串类型字段
• in_语法是判断字段值是否存在于给定列表中，不适用ARRAY类型字段的元素匹配

内容的提问来源于stack exchange，提问作者C. S. F. Junior