title: Using JSON Type with Flask-sqlalchemy & Postgresql
body: <p><strong>Background:</strong>  I am building a Flask App and I have stored my data into a postgresql database and within a JSON column type. </p>
  
  <p><strong>Task:</strong>  In my view functions, I would like to order a database query by {Key:Value} from JSON column</p>
  
  <p><strong>Accomplished</strong>:  I have been successful in performing this query at the psql command-line by using the following command for example:</p>
  
  <p>select * from target where cast(product->>'profit' as float) > 100 order by cast(product->>'salesrank' as integer) asc;</p>
  
  <p><strong>Problem:</strong> I can not replicate this query in my code <strong>(see code for Model below in Extra Info Section)</strong></p>
  
  <pre><code>from app import app, db
  from models import Target 
  
  data = Target.query.order_by(Target.product['salesrank'])
  </code></pre>
  
  <p><strong>Error received</strong> - 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}</p>
  
  <p><strong>Extra Info</strong>
  My Target model was set up as such:</p>
  
  <pre><code>#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())
  </code></pre>
  
  <p>My App.py file where I define Flask and Sqlalchemy</p>
  
  <pre><code>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)
  </code></pre>
  
  <p><strong>Thank you for any help you can provide!</strong></p>