使用sqlalchemy查询特定的JSON列(postgres) [英] Query a specific JSON column (postgres) with sqlalchemy
问题描述
class Item(db.Model)
...
data = db.Column(JSON,nullable = False)
...
<这些数据包含一些JSON,例如:
$
$ cost $ 10
$乘客:2,
附加费:1.6
}
能够通过过滤器获得表格中所有行的成本总和。
db.session.query(func.count(Item.data ('$ cost']))。filter(
Item.data [surcharge]。cast(Float)> 1
).scalar()
您使用的是聚合。 count(expression)
计算表达式不为null的行数。如果你想要一个总和,可以用 sum(expression)
:
$ $ p $ db .session.query(func.sum(Item.data ['cost']。astext.cast(Numeric)))。\
filter(Item.data ['surcharge']。astext.cast(Numeric) > 1)。\
scalar()
请注意,货币值和二进制浮点由于二进制浮点数不能表示所有十进制值, 。请使用适当的货币类型或 在这种情况下,SQLAlchemy使用 Decimal
来表示Python中的结果。
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()
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.
这篇关于使用sqlalchemy查询特定的JSON列(postgres)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!