Dave Gallant Dave Gallant - 1 month ago 9
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

Try using astext

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