Dave Gallant Dave Gallant - 10 months ago 63
JSON Question

Python SQLAlchemy and Postgres - How to query a JSON element

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 Source

Try using astext

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