Python SQLAlchemy 和 Postgres - 如何查询 JSON 元素
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29974143/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Python SQLAlchemy and Postgres - How to query a JSON element
提问by Dave Gallant
Let's say I have a Postgres database (9.3) and there is a table called Resources. In the Resourcestable I have the fields idwhich is an int and datawhich is a JSON type.
假设我有一个 Postgres 数据库 (9.3) 并且有一个名为Resources. 在Resources表中，我有id一个 int 和dataJSON 类型的字段。
Let's say I have the following records in said table.
假设我在所述表中有以下记录。
- 1, {'firstname':'Dave', 'lastname':'Gallant'}
- 2, {'firstname':'John', 'lastname':'Doe'}
- 1, {'firstname':'Dave', 'lastname':'Gallant'}
- 2, {'firstname':'John', 'lastname':'Doe'}
What I want to do is write a query that would return all the records in which the data column has a json element with the lastname equal to "Doe"
我想要做的是编写一个查询，该查询将返回数据列具有 json 元素且姓氏等于“Doe”的所有记录
I tried to write something like this:
我试着写这样的东西：
records = db_session.query(Resource).filter(Resources.data->>'lastname' == "Doe").all()
Pycharm however is giving me a compile error on the "->>"
然而，Pycharm 在“->>”上给了我一个编译错误
Does anyone know how I would write the filter clause to do what I need?
有谁知道我将如何编写过滤器子句来满足我的需要？
采纳答案by Anzel
回答by kc41
Also you could explicitly cast string to JSON (see Postgres JSON type doc).
您也可以将字符串显式转换为 JSON（请参阅Postgres JSON type doc）。
from sqlalchemy.dialects.postgres import JSON
from sqlalchemy.sql.expression import cast
db_session.query(Resource).filter(
Resources.data["lastname"] == cast("Doe", JSON)
).all()
回答by user1900344
According sqlalchemy.types.JSON, you can do it like this
根据sqlalchemy.types.JSON，你可以这样做
from sqlalchemy import JSON
from sqlalchemy import cast
records = db_session.query(Resource).filter(Resources.data["lastname"] == cast("Doe", JSON)).all()
回答by Andrew Allen
If you are using JSON type (not JSONB) the following worked for me:
如果您使用的是 JSON 类型（不是 JSONB），以下内容对我有用：
Note the '"object"'
请注意 '"object"'
query = db.session.query(ProductSchema).filter(
cast(ProductSchema.ProductJSON["type"], db.String) != '"object"'
)
