sqlalchemy filter by json field

by

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.

Leave a Comment