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

Answer 3

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

Answer 4

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

Answer 5

According sqlalchemy.types.JSON, you can do it like this

from sqlalchemy import JSON
from sqlalchemy import cast
records = db_session.query(Resource).filter(Resources.data["lastname"] == cast("Doe", JSON)).all()

Answer 6

According to this, pre version 1.3.11, the most robust way should be like this, as it works for multiple database types, e.g. SQLite, MySQL, Postgres:

from sqlalchemy import cast, JSON, type_coerce, String

db_session.query(Resource).filter(
    cast(Resources.data["lastname"], String) == type_coerce("Doe", JSON)
).all()

From version 1.3.11 onward, type-specific casters is the new and neater way to handle this:

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