CODEWITHSUNDEEP
pythonsqlalchemyflask-sqlalchemy
Danila Ganchar
Danila Ganchar

Reputation: 11223

sqlalchemy filter by json field

I have model with json column. Example of model and data:

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'postgres://...'

db = SQLAlchemy()
db.init_app(app)
app.app_context().push()

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

db.create_all()
db.session.add(Example(json_field={'id': None}))
db.session.add(Example(json_field={'id': 1}))
db.session.add(Example(json_field={'id': 50}))
db.session.add(Example(json_field={}))
db.session.commit()

Now I try to find records where id == 1:

query = db.session.query(Example).filter(Example.json_field['id'] == 1)
print(query.all())

And I getting the next error:

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) operator does not exist: json = integer LINE 3: WHERE (example.json_field -> 'id') = 1

The reason. Look at generated query:

SELECT example.id AS example_id, example.json_field AS example_json_field 
FROM example 
WHERE (example.json_field -> %(json_field_1)s) = %(param_1)s

But in my case correct query should be like this:

SELECT * FROM example WHERE CAST(json_field->>'id' AS INTEGER) = 1;

How can I do this?

I have tried use cast, but unsuccessfully:

print(
    db.session.query(Example).filter(
        cast(Example.json_field['id'], Integer) == 1
    ).all()
)

The error:

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) cannot cast type json to integer LINE 3: WHERE CAST((example.json_field -> 'id') AS INTEGER) = 1

As you can see where clause still wrong. Also I need to use range (>, <= etc.) conditions. Thanks for help.

Upvotes: 35

Views: 59437

Answers (5)

DINA TAKLIT
DINA TAKLIT

Reputation: 8388

If you want to verify with multiple data you can do something like this:

query = query.filter(
    SuggestionValue.raw_data.op("->>")('metric_row_priority').in_(filters["hcp_priorities"])
)

where raw_data is column with type Column(JSON, nullable=True) and filters["hcp_priorities"] is an array of string values, ie. ["P1", "P2"]

Upvotes: 0

Serhii
Serhii

Reputation: 317

I guess you can use this expression:

db.session.query(Example).filter(
    Example.json_field.op("->>")("id").cast(Integer) == 1
)

Upvotes: 7

Ryabchenko Alexander
Ryabchenko Alexander

Reputation: 12370

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

Upvotes: 10

eastonsuo
eastonsuo

Reputation: 1079

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.

Upvotes: 4

Ilja Everil&#228;
Ilja Everil&#228;

Reputation: 52929

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.

Upvotes: 62

Related Questions