sqlalchemy 按 json 字段过滤 [英] sqlalchemy filter by json field
问题描述
我有带有 json 列
的模型.模型和数据示例:
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())
然后我收到下一个错误:
And I getting the next error:
sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) 操作符不存在:json = integer 第 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') 作为整数) = 1
sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) cannot cast type json to integer LINE 3: WHERE CAST((example.json_field -> 'id') AS INTEGER) = 1
如您所见,where 子句
仍然是错误的.我还需要使用范围(>
、<=
等)条件.感谢您的帮助.
As you can see where clause
still wrong. Also I need to use range (>
, <=
etc.) conditions. Thanks for help.
推荐答案
Flask-SQLAlchemy 的 SQLAlchemy
对象 – 通常命名为 db
– 允许访问来自 sqlalchemy
和 sqlalchemy 的函数等.orm
,所以 db.JSON
是 通用 JSON
类型,不提供 Postgresql 特定的运算符.您应该改用 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)
使用适当的类型,您必须显式转换 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 onJSON
objects now requires that theJSON.Comparator.astext
modifier be called explicitly, if the cast works only from a textual string.
这篇关于sqlalchemy 按 json 字段过滤的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!