Join the Stack Overflow Community
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other.
Join them; it only takes a minute:
Sign up

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!

share|improve this question
up vote 10 down vote accepted

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 

data = Target.query.order_by(Target.product['salesrank'].cast(Integer))
share|improve this answer
    
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 May 27 '14 at 8:03
    
or Integer from sqlalchemy.types as you have suggested in order to make it work. – larrywgray May 27 '14 at 8:11

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.