Query A Specific Json Column (postgres) With Sqlalchemy

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, 'passe

Solution 1:

You're using the wrong aggregate. count(expression) counts the number of rows for which the expression is not null. If you want a 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 that monetary values and binary floating point math is a bad mixture due to binary floats not being able to represent all decimal values. Instead use a proper monetary type, or Numeric in which case SQLAlchemy uses Decimal to represent the results in Python.

Solution 2:

You need to use Float instead of Integer as argument of cast

db.session.query(func.count(Item)).filter(
          Item.data['surcharge'].cast(Float) >1
      ).all()