如何在SQLAlchemy ORM中对JSONB列进行过滤查询

你的User模型中params字段是PostgreSQL的JSONB类型，存储对象数组，要筛选出params里包含{"a": "333"}的用户，转文本用ilike的方式容易出现误匹配（比如其他字段值含"333"也会被命中），且无法利用JSONB索引，推荐用PostgreSQL原生的JSONB查询能力，SQLAlchemy支持直接调用这些特性：

方法一：使用jsonb_array_elements结合子查询 #

通过展开JSONB数组，检查是否存在符合条件的元素：

from sqlalchemy import exists, func

# 查询所有params数组中包含{"a": "333"}的用户
target_users = session.query(User).filter(
    exists().where(
        func.jsonb_array_elements(User.params).op('@>')({"a": "333"})
    )
).all()

• jsonb_array_elements：将JSONB数组展开为行
• @>：PostgreSQL的JSONB包含操作符，判断展开后的元素是否包含指定的键值对

方法二：使用JSON Path查询 #

PostgreSQL 12+支持JSON Path语法，写法更直观：

# 使用JSON Path匹配数组中存在a为333的元素
target_users = session.query(User).filter(
    func.jsonb_path_exists(User.params, '$.[] ? (@.a == "333")')
).all()

• $.[]：遍历数组中的所有元素
• ? (@.a == "333")：筛选出a属性等于"333"的元素

性能优化建议 #

如果这个查询比较频繁，建议给params字段创建GIN索引：

CREATE INDEX idx_users_params_gin ON users USING GIN (params jsonb_path_ops);

这样上面的查询可以利用索引大幅提升性能。

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