python - Query a specific JSON column (postgres) with sqlalchemy -


i have model json field:

class item(db.model)    ...    data = db.column(json, nullable=false)    ... 

the data contains json such as:

{     "cost": 10.00,     "passengers": 2,     "surcharge": 1.6 } 

i want able sum of cost across rows in table filter. tried following didn't seem work.

db.session.query(func.count(item.data['cost'])).filter(           item.data["surcharge"].cast(float) > 1       ).scalar() 

you're using wrong aggregate. count(expression) counts number of rows expression not null. if want sum, use sum(expression):

db.session.query(func.sum(item.data['cost'].astext.cast(numeric))).\     filter(item.data['surcharge'].astext.cast(numeric) > 1).\     scalar() 

note monetary values , binary floating point math bad mixture due binary floats not being able represent decimal values. instead use proper monetary type, or numeric in case sqlalchemy uses decimal represent results in python.


Comments

Popular posts from this blog

android - InAppBilling registering BroadcastReceiver in AndroidManifest -

python Tkinter Capturing keyboard events save as one single string -

sql server - Why does Linq-to-SQL add unnecessary COUNT()? -