sqlalchemy按json字段过滤 [英] sqlalchemy filter by json field

问题描述

我有一个带有json column的模型.模型和数据示例:

I have model with json column. Example of model and data:

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中找到记录:

Now I try to find records where id == 1:

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

我收到下一个错误:

sqlalchemy.exc.ProgrammingError:(psycopg2.ProgrammingError)运算符 不存在:json =整数第3行:WHERE(example.json_field-> 'id')= 1

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) operator does not exist: 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

但是在我的情况下，正确的查询应该是这样的:

But in my case correct query should be like this:

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

我该怎么做?

我尝试使用 cast ，但未成功:

I have tried use cast, but unsuccessfully:

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

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) cannot cast type json to integer LINE 3: WHERE CAST((example.json_field -> 'id') AS INTEGER) = 1

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

As you can see where clause still wrong. Also I need to use range (>, <= etc.) conditions. Thanks for help.

推荐答案

Flask-SQLAlchemy的SQLAlchemy对象–通常命名为db – sqlalchemy.dialects.postgresql.JSON :

Flask-SQLAlchemy's SQLAlchemy object – commonly named db – gives access to functions etc. from sqlalchemy and sqlalchemy.orm, and so db.JSON is the generic JSON type that does not provide the Postgresql specific operators. You should instead use 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)

使用适当的类型后，您必须显式转换

With the proper type in place you must explicitly convert the JSON to text first and then cast to an integer:

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版及更高版本中已将其删除:

The same applies to all types that cannot be directly cast from json. SQLAlchemy used to offer a shortcut for the combination of astext and cast(), but it has been removed in version 1.1 and above:

在版本1.1中进行了更改:JSON对象上的ColumnElement.cast()运算符现在要求，如果强制转换仅从文本字符串起作用，则必须显式调用JSON.Comparator.astext修饰符.

Changed in version 1.1: The ColumnElement.cast() operator on JSON objects now requires that the JSON.Comparator.astext modifier be called explicitly, if the cast works only from a textual string.

这篇关于sqlalchemy按json字段过滤的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！