Query a specific JSON column (postgres) with sqlalchemy

Viewed 4228

I have a model with a JSON field:

class Item(db.Model)
   ...
   data = db.Column(JSON, nullable=False)
   ...

The data contains some JSON such as:

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

I want to be able to get a sum of the cost across all rows in the table with a filter. I tried the following but that didn't seem to work.

db.session.query(func.count(Item.data['cost'])).filter(
          Item.data["surcharge"].cast(Float) > 1
      ).scalar()
2 Answers
Related