对另一个组中的项目进行分组
对另一个组中的项目进行分组
提问于 2020-06-02 09:59:42
我想按类别对以下集合进行分组，并对其总值求和，然后在相同的结构上创建子类别属性，如果多个类别相等，则对子类别求和。
[
{
"_id": 1,
"date": ISODate("2019-10-10T00:00:00.000Z"),
"description": "INTERNET BILL",
"credit": "",
"debit": "-100.00",
"category": "home",
"subcategory": "internet",
"__v": 0
},
{
"_id": 2,
"date": ISODate("2019-10-10T00:00:00.000Z"),
"description": "WATER BILL",
"credit": "",
"debit": "-150.00",
"category": "home",
"subcategory": "water",
"__v": 0
},
{
"_id": 3,
"date": ISODate("2019-10-10T00:00:00.000Z"),
"description": "MC DONALDS",
"credit": "",
"debit": "-30.00",
"category": "food",
"subcategory": "restaurants",
"__v": 0
},
{
"_id": 4,
"date": ISODate("2019-10-10T00:00:00.000Z"),
"description": "BURGER KING",
"credit": "",
"debit": "-50.00",
"category": "food",
"subcategory": "restaurants",
"__v": 0
},
{
"_id": 5,
"date": ISODate("2019-10-10T00:00:00.000Z"),
"description": "WALMART",
"credit": "",
"debit": "-20.00",
"category": "food",
"subcategory": "groceries",
"__v": 0
},
]可靠的输出：
[
{
"_id": "home",
"total": "-250.00",
"subcategory" : [
{"id": "internet", "total": "-100"},
{"id": "water", "total": "-150"}
]
},
{
"_id": "food",
"total": "-100.00",
"subcategory" : [
{"id": "restaurants", "total": "-80"},
{"id": "groceries", "total": "-20"}
]
}
]通过以下查询，我几乎实现了这一点，但我还没有找到对子类别求和的方法。
db.getCollection('expenses').aggregate([
{$match:
{"date" : { "$gte" : new Date("11-10-2019"), "$lte": new Date("2019-10-11") }}
},
{$group: {
_id: "$category",
total: { $sum: { $toDouble: { $cond: { if: { $ne: [ "$debit", ""] }, then: "$debit", else: "$credit" } } } },
subcategories: { $addToSet: {id: "$subcategory" }},
}}
])回答 1
Stack Overflow用户
回答已采纳
发布于 2020-06-02 10:34:59
您可以$group两次(首先按子类别)：
db.collection.aggregate([
{
$group: {
_id: { category: "$category", subcategory: "$subcategory" },
total: { $sum: { $toDouble: "$debit" } }
}
},
{
$group: {
_id: "$_id.category",
total: { $sum: "$total" },
subcategories: { $push: { id: "$_id.subcategory", total: "$total" } }
}
}
])页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/62143631
复制相关文章
相似问题
腾讯云开发者
