SqlAlchemy如何基于NestedMutableJson类型字段的键属性查询表

解决方案1：转成原生JSON类型后查询 #

你遇到的报错本质是NestedMutableJson作为第三方扩展类型，没有原生支持SQLAlchemy的JSON下标查询操作，将字段强转为SQLAlchemy内置的JSON类型即可正常使用下标查询：

from sqlalchemy import cast, JSON

# 查询语句
result = User.query.filter(
    cast(User.country_info, JSON)['name'].astext == 'UK'
).all()

这个方案生成的SQL和你写的原生PostgreSQL语句完全一致。

解决方案2：调用PostgreSQL原生JSON函数 #

如果强转方案不生效，可以直接调用PostgreSQL内置的json_extract_path_text函数实现相同逻辑，兼容性更好：

from sqlalchemy import func

# 查询语句
result = User.query.filter(
    func.json_extract_path_text(User.country_info, 'name') == 'UK'
).all()

问题原因说明 #

• 方案1报错：sqlalchemy_json库提供的NestedMutableJson核心能力是支持嵌套JSON的原地修改、自动识别数据变更，没有实现SQLAlchemy原生JSON类型的__getitem__查询重载，所以直接用[]取值会触发Operator 'getitem' is not supported报错。
• 方案2返回空：通过op('->>')自定义操作符的方式，可能存在隐式类型转换问题，导致生成的SQL语句和你预期的原生语句存在差异，最终匹配不到数据。

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