Query a specific JSON column (postgres) with sqlalchemy

Question

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

Ilja Everilä · Accepted Answer · 2017-08-15 11:35:10Z

10

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.

edited Aug 15, 2017 at 11:35

answered Aug 15, 2017 at 11:01

Ilja Everilä

53.4k9 gold badges137 silver badges141 bronze badges

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

2 Comments

Antti Haapala Over a year ago

Though PostgreSQL JSONB stores numbers in numeric, and JSON as decimal strings... However, needs some special handling with Python ...

Urik Over a year ago

Great answer but I had to use as_string() instead of astext (SQLAlchemy 1.3.23)

vishes_shell · Accepted Answer · 2017-08-15 09:05:34Z

0

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

answered Aug 15, 2017 at 9:05

vishes_shell

23.8k7 gold badges74 silver badges82 bronze badges

1 Comment

skittles789 Over a year ago

Fair point, I've updated the query but that still doesn't work. It just returns 0.

Collectives™ on Stack Overflow

Query a specific JSON column (postgres) with sqlalchemy

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related