时间戳

首页 > 解决方案 > sqlalchemy filter by json field

python - sqlalchemy filter by json field

问题描述

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()

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

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

And I getting the next error:

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) operator does not exist: json = integer LINE 3: WHERE (example.json_field -> 'id') = 1

The reason. Look at generated query:

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;

How can I do this?

I have tried use cast, but unsuccessfully:

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

The error:

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

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

标签： pythonsqlalchemyflask-sqlalchemy

解决方案

Flask-SQLAlchemy 的SQLAlchemy对象 - 通常命名db-提供对函数等的访问sqlalchemy和sqlalchemy.orm，不提供 Postgresql 特定运算符db.JSON的泛型JSON类型也是如此。您应该改为使用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 曾经为astextand的组合提供快捷方式cast()，但在 1.1 及更高版本中已将其删除：

在 1.1 版更改：对象ColumnElement.cast()上的运算符JSON现在要求JSON.Comparator.astext显式调用修饰符，如果强制转换仅适用于文本字符串。

推荐阅读