sqlalchemy filter by json field

if you only use filter(json_obj["key"] ... ) it will convert to sql like model_name.json_obj -> 'key', which is still a json object,

if you use filter(json_obj["key"].astext ...), the sql will be model_name.json_obj ->> 'key', the result is a string object.

Flask-SQLAlchemy's SQLAlchemy object – commonly named db – gives access to functions etc. from sqlalchemy and sqlalchemy.orm, and so db.JSON is the generic JSON type that does not provide the Postgresql specific operators. You should instead use sqlalchemy.dialects.postgresql.JSON:

from sqlalchemy.dialects.postgresql import JSON

class Example(db.Model):
    id = db.Column(db.Integer(), nullable=False, primary_key=True, )
    json_field = db.Column(JSON)

With the proper type in place you must explicitly convert the JSON to text first and then cast to an integer:

db.session.query(Example).\
    filter(Example.json_field['id'].astext.cast(Integer) == 1)

This produces the desired predicate

CAST(json_field->>'id' AS INTEGER) = 1

The same applies to all types that cannot be directly cast from json. SQLAlchemy used to offer a shortcut for the combination of astext and cast(), but it has been removed in version 1.1 and above:

Changed in version 1.1: The ColumnElement.cast() operator on JSON objects now requires that the JSON.Comparator.astext modifier be called explicitly, if the cast works only from a textual string.

You can also use raw sql in filter

from sqlalchemy import text

db.session.query(Example).filter(text("CAST(json_field->>'id' AS INTEGER) = 1")