Using JSON Type with Flask-sqlalchemy & Postgresql

Question

Background: I am building a Flask App and I have stored my data into a postgresql database and within a JSON column type.

Task: In my view functions, I would like to order a database query by {Key:Value} from JSON column

Accomplished: I have been successful in performing this query at the psql command-line by using the following command for example:

select * from target where cast(product->>'profit' as float) > 100 order by cast(product->>'salesrank' as integer) asc;

Problem: I can not replicate this query in my code (see code for Model below in Extra Info Section)

from app import app, db
from models import Target 

data = Target.query.order_by(Target.product['salesrank'])

Error received - ProgrammingError: (ProgrammingError) could not identify an ordering operator for type json LINE 2: FROM target ORDER BY target.product -> 'salesrank' ^ HINT: Use an explicit ordering operator or modify the query. 'SELECT target.id AS target_id, target.store AS target_store, target.product AS target_product, target.asin AS target_asin, target.date AS target_date \nFROM target ORDER BY target.product -> %(product_1)s \n LIMIT %(param_1)s' {'product_1': 'salesrank', 'param_1': 1}

Extra Info My Target model was set up as such:

#models.py
from app import db
from sqlalchemy.dialects.postgresql import JSON
import datetime

class Target(db.Model):
    __tablename__ = 'target'

    id = db.Column(db.Integer)
    store = db.Column(db.String())
    product = db.Column(JSON)
    asin = db.Column(db.String(), primary_key=True)
    date = db.Column(db.DateTime, default=datetime.datetime.utcnow())

My App.py file where I define Flask and Sqlalchemy

from flask import Flask
import os
from flask.ext.sqlalchemy import SQLAlchemy
from flask_bootstrap import Bootstrap

app = Flask(__name__)
app.config.from_object(os.environ['APP_SETTINGS'])
db = SQLAlchemy(app)
Bootstrap(app)

import views
from app import app
from models import Result

if __name__ == '__main__':
    app.run(host='192.168.1.5', port=5000, debug=True)

Thank you for any help you can provide!

Solution

Looking at the SQLAlchemy documentation for the JSON data type it appears that you should be able to use the .cast method:

from sqlalchemy.types import Integer

from app import app, db
from models import Target 

# SQLAlchemy 1.1+
data = Target.query.order_by(Target.product['salesrank'].astext.cast(Integer))

# SQLAlchemy < 1
data = Target.query.order_by(Target.product['salesrank'].cast(Integer))

Comments

[larrywgray]

I came across this in the docs, tried this several times before but only got "Integer" unknown error --- I now realized that I just needed to import INTEGER from sqlalchemy.dialects.postgresql to make it work. Correct code is:

[larrywgray]

or Integer from sqlalchemy.types as you have suggested in order to make it work.

[Ilja Everilä]

Since version 1.1 the JSONElement.cast() shortcut is gone: "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."