How to query a JSON element

Solution 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.

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

Solution 3:

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

Solution 4:

I have some GeoJSON in a JSON (not JSONB) type column and none of the existing solutions worked, but as it turns out, in version 1.3.11 some new data casters were added, so now you can:

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

Reference: https://docs.sqlalchemy.org/en/14/core/type_basics.html#sqlalchemy.types.JSON

Casting JSON Elements to Other Types

Index operations, i.e. those invoked by calling upon the expression using the Python bracket operator as in some_column['some key'], return an expression object whose type defaults to JSON by default, so that further JSON-oriented instructions may be called upon the result type. However, it is likely more common that an index operation is expected to return a specific scalar element, such as a string or integer. In order to provide access to these elements in a backend-agnostic way, a series of data casters are provided:

Comparator.as_string() - return the element as a string

Comparator.as_boolean() - return the element as a boolean

Comparator.as_float() - return the element as a float

Comparator.as_integer() - return the element as an integer

These data casters are implemented by supporting dialects in order to assure that comparisons to the above types will work as expected, such as:

# integer comparison
data_table.c.data["some_integer_key"].as_integer() == 5

# boolean comparison
data_table.c.data["some_boolean"].as_boolean() == True