Python SQLAlchemy and Postgres - How to query a JSON element

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.


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()

If you are using JSON type (not JSONB) the following worked for me:

Note the '"object"'

    query = db.session.query(ProductSchema).filter(
        cast(ProductSchema.ProductJSON["type"], db.String) != '"object"'
    )