如何在SQLAlchemy中实现PostgreSQL JSON列的大小写不敏感模糊查询

核心思路 #

对应你原生SQL里的LOWER(data::text) LIKE '%<keyword>%'，在SQLAlchemy里需要完成两个关键操作：

1. 将JSON列显式转换为TEXT类型（对应SQL的::text）
2. 对转换后的文本执行小写转换，再匹配通配符

正确实现方式 #

方法1：使用cast+func.lower+like（最贴近原生SQL）

用cast()函数将JSON列转为TEXT类型，再通过func.lower()处理大小写，最后用like()匹配通配符：

from sqlalchemy import func, cast, TEXT
from your_module import MyTable, session

search_keyword = "your_search_term"
# 安全拼接通配符（避免SQL注入风险）
pattern = func.concat('%', search_keyword, '%')

query = session.query(MyTable).filter(
    func.lower(cast(MyTable.data, TEXT)).like(pattern)
)

方法2：直接用ilike简化写法

PostgreSQL的ILIKE本身支持大小写不敏感匹配，无需手动转小写，写法更简洁：

from sqlalchemy import cast, TEXT
from your_module import MyTable, session

search_keyword = "your_search_term"
pattern = func.concat('%', search_keyword, '%')

query = session.query(MyTable).filter(
    cast(MyTable.data, TEXT).ilike(pattern)
)

方法3：使用text()直接编写原生SQL片段

如果更习惯原生SQL语法，也可以直接传入SQL文本，配合参数绑定避免注入：

from sqlalchemy import text
from your_module import MyTable, session

search_keyword = "your_search_term"

query = session.query(MyTable).filter(
    text("LOWER(data::text) LIKE :pattern"),
    {"pattern": f"%{search_keyword}%"}
)

为什么你的写法报错？ #

你之前用的Table.data.text()不是SQLAlchemy中转换JSON列到TEXT的正确方式，这个方法通常用于字符串类列获取文本属性。正确的类型转换需要用cast()函数，或者针对JSONB列可以用.astext属性（不过cast()对JSON/JSONB都通用）。

内容的提问来源于stack exchange，提问作者Roberto