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!

although slightly unrelated. I'm having issues with JSON but not with querying but with the initial insert of data. Do you know how you inserted the data in to the JSON field. see my question for more details. stackoverflow.com/questions/55628358/… — demonLaMagra
– demonLaMagra, Commented Apr 11, 2019 at 11:25

Sean Vieira · Accepted Answer · 2018-11-15 06:44:59Z

30

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

edited Nov 15, 2018 at 6:44

answered May 27, 2014 at 5:12

Sean Vieira

161k34 gold badges321 silver badges297 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

larrywgray Over a year ago

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 Over a year ago

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

Ilja Everilä Over a year ago

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

Collectives™ on Stack Overflow

Using JSON Type with Flask-sqlalchemy & Postgresql

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related