How to query a JSON element

Question

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'}

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"

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 "->>"

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

Answer 1

Try using astext

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

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

Answer 2

Also you could explicitly cast string to JSON (see 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()