order by JSON data type SQLAlchemy-CodePudding

I have a Postgres table that has a JSON type column that has a bunch of JSON objects in it. I want to query the records of a table and order the results by key value stored in a JSON field.

row1 {'2011': 600, '2012': 100, '2014': 200}
row2 {'2011': 700, '2012': 100, '2014': 200}
row3 {'2011': 500, '2012': 100, '2014': 200}

How can I make a request like this:

Table.query.order_by(Table.data['2011']).all()

Whith result:

500
600
700

I have tried the queries:

Table.query.order_by(Table.data.cast(JSON)['2011']).all()
Table.query.order_by(Table.data(JSON)['2011']).all()

But I always get various errors

CodePudding user response：

You could do this by extracting the value as text and then casting to an integer. Precisely how to do this depends on the column type in the database and in the model.

If the column type in the database is JSON, and the column type in the model is SQLAlchemy's generic JSON type, you need to cast the value to a text type, and then to an numeric type.

# Get these from the db namespace if using Flask-SQLAlchemy
from sqlalchemy import cast, Integer, String
...
Table.query.order_by(cast(cast(tbl.c.data['2011'], String), sa.Integer)).all()

If the column in the database is JSON, and the column type in the model is the JSON type from SQLAlchemy's PostgresSQL dialect, you can use the column's astext attribute instead of casting to a text type. (You can change the column type from generic JSON to Postgres JSON in the model with having to migrate).

from sqlalchemy.dialects.postgresql import JSON
...
Table.query.order_by(cast(Table.data['2011'].astext, Integer)).all()

If the column type in the database is JSONB and the column type in the model is JSONB, you can use the value directly:

from sqlalchemy.dialects.postgresql import JSONB
...
Table.query.order_by(Table.data['2011']).all()