Python SQLAlchemy和Postgres-如何查询JSON元素 [英] Python SQLAlchemy and Postgres - How to query a JSON element

问题描述

假设我有一个Postgres数据库(9.3)，并且有一个名为Resources的表.在Resources表中，我有一个字段id(它是一个整数)和data这是一个JSON类型.

Let's say I have a Postgres database (9.3) and there is a table called Resources. In the Resources table I have the fields id which is an int and data which is a JSON type.

假设我在上述表格中有以下记录.

Let's say I have the following records in said table.

• 1，{'firstname':'Dave'，'lastname':'Gallant'}
• 2，{'firstname':'John'，'lastname':'Doe'}

我想做的是编写一个查询，该查询将返回所有记录，其中数据列具有一个json元素，其姓氏等于"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"

我试图写这样的东西:

records = db_session.query(Resource).filter(Resources.data->>'lastname' == "Doe").all()

Pycharm给了我->>"的编译错误

Pycharm however is giving me a compile error on the "->>"

有人知道我将如何编写filter子句来完成所需的工作吗?

Does anyone know how I would write the filter clause to do what I need?

推荐答案

尝试使用 astext

records = db_session.query(Resource).filter(
              Resources.data["lastname"].astext == "Doe"
          ).all()

请注意，该列必须具有JSONB类型.常规JSON列将不起作用.

Please note that the column MUST have a type of a JSONB. The regular JSON column will not work.

这篇关于Python SQLAlchemy和Postgres-如何查询JSON元素的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！