sqlalchemy 按 json 字段过滤

Question

我有模型 json column 。模型和数据示例：

 app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'postgres://...'

db = SQLAlchemy()
db.init_app(app)
app.app_context().push()

class Example(db.Model):
    id = db.Column(db.Integer(), nullable=False, primary_key=True, )
    json_field = db.Column(db.JSON())

db.create_all()
db.session.add(Example(json_field={'id': None}))
db.session.add(Example(json_field={'id': 1}))
db.session.add(Example(json_field={'id': 50}))
db.session.add(Example(json_field={}))
db.session.commit()

现在我尝试在 id == 1 中查找记录：

 query = db.session.query(Example).filter(Example.json_field['id'] == 1)
print(query.all())

我收到下一个错误：

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) 运算符不存在：json = integer LINE 3: WHERE (example.json_field -> ‘id’) = 1

原因。 查看生成的查询：

 SELECT example.id AS example_id, example.json_field AS example_json_field
FROM example
WHERE (example.json_field -> %(json_field_1)s) = %(param_1)s

但就我而言，正确的查询应该是这样的：

 SELECT * FROM example WHERE CAST(json_field->>'id' AS INTEGER) = 1;

我怎样才能做到这一点？

我试过使用 cast ，但没有成功：

 print(
    db.session.query(Example).filter(
        cast(Example.json_field['id'], Integer) == 1
    ).all()
)

错误：

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) 无法将类型 json 转换为整数 第 3 行：WHERE CAST((example.json_field -> ‘id’) AS INTEGER) = 1

如您所见 where clause 仍然是错误的。我还需要使用范围（ > ， <= 等）条件。感谢帮助。

原文由 Danila Ganchar 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

Flask-SQLAlchemy’s SQLAlchemy object – commonly named db – gives access to functions etc. from sqlalchemy and sqlalchemy.orm , and so db.JSON 是 通用的 JSON 不提供 Postgresql 特定运算符的类型。您应该改为使用 sqlalchemy.dialects.postgresql.JSON ：

 from sqlalchemy.dialects.postgresql import JSON

class Example(db.Model):
    id = db.Column(db.Integer(), nullable=False, primary_key=True, )
    json_field = db.Column(JSON)

使用适当的类型后，您必须先将 JSON 显式转换为文本，然后再转换为整数：

 db.session.query(Example).\
    filter(Example.json_field['id'].astext.cast(Integer) == 1)

这会产生所需的谓词

CAST(json_field->>'id' AS INTEGER) = 1

这同样适用于不能直接从 json 转换的所有类型。 SQLAlchemy 曾经为 astext 和 cast() 的组合提供快捷方式，但它已在 1.1 及更高版本中删除：

在 1.1 版更改： ColumnElement.cast() 上的运算符 JSON 对象现在要求 --- 对象现在需要 JSON.Comparator.astext 仅当从文本字符串显式调用修饰符时才起作用。

原文由 Ilja Everilä 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 2

您还可以在过滤器中使用原始 sql

 from sqlalchemy import text

db.session.query(Example).filter(text("CAST(json_field->>'id' AS INTEGER) = 1")

原文由 Ryabchenko Alexander 发布，翻译遵循 CC BY-SA 4.0 许可协议