MongoDB使用$map$group，条件使用聚合函数

Question

我有一个集合资金和基金交易，根据交易方法和类型查询余额和总额。下面的查询得到了添加余额的正确结果，但我对分组和预测结果有问题。

此外，我只想使用$map改进我的查询，并投影那些我想显示的字段

资金收集

"funds": [
{
"_id": ObjectId("623dd490c00eec15c805920b"),
balance: 160
}
]

基金交易集合

"fundTransactions": [
{
"_id": ObjectId("623dd490c00eec15c805921a"),
"fundID": ObjectId("623dd490c00eec15c805920b"),
"transactionMethods": "Cash",
"transactionType": "Deposit",
"amount": 100
},
{
"_id": ObjectId("623dd490c00eec15c805931f"),
"fundID": ObjectId("623dd490c00eec15c805920b"),
"transactionMethods": "EBT",
"transactionType": "Deposit",
"amount": 20
},
{
"_id": ObjectId("623dd490c00eec15c805733d"),
"fundID": ObjectId("623dd490c00eec15c805920b"),
"transactionMethods": "Cash",
"transactionType": "Expense",
"amount": 10
},
{
"_id": ObjectId("623dd490c00eec15c805733c"),
"fundID": ObjectId("623dd490c00eec15c805920b"),
"transactionMethods": "Debit Card",
"transactionType": "Deposit",
"amount": 50
},
]

聚合函数

db.funds.aggregate([
{
"$lookup": {
"from": "fundTransactions",
"localField": "_id",
"foreignField": "fundID",
"as": "funds"
}
},
{
"$unwind": {
"path": "$funds"
}
},
{
"$group": {
"_id": {
"funds": "$funds.transactionMethods"
},
"totalBalance": {
"$sum": {
"$cond": [
{
"$eq": [
"$funds.transactionType",
"Deposit"
]
},
"$funds.amount",
{
"$multiply": [
"$funds.amount",
-1.0
]
}
]
}
},
"Expense": {
"$sum": {
"$cond": [
{
"$eq": [
"$funds.transactionType",
"Expense"
]
},
"$funds.amount",
0.0
]
}
}
}
},
{
"$addFields": {
"EBT": {
"$cond": [
{
"$eq": [
"$_id.funds",
"EBT"
]
},
{
"$round": [
"$totalBalance",
0.0
]
},
0.0
]
},
"Cash": {
"$cond": [
{
"$eq": [
"$_id.funds",
"Cash"
]
},
{
"$round": [
"$totalBalance",
0.0
]
},
0.0
]
},
"Debit Card": {
"$cond": [
{
"$eq": [
"$_id.funds",
"Debit Card"
]
},
{
"$round": [
"$totalBalance",
0.0
]
},
0.0
]
},
"Expense": {
"$round": [
"$Expense",
0.0
]
}
}
},
{
"$project": {
"_id": 0.0,
"totalBalance": 0.0
}
}
])

这是我使用此查询的当前输出

[
{
"Cash": 90,
"Debit Card": 0,
"EBT": 0,
"Expense": 10
},
{
"Cash": 0,
"Debit Card": 0,
"EBT": 20,
"Expense": 0
},
{
"Cash": 0,
"Debit Card": 50,
"EBT": 0,
"Expense": 0
}
]

我的预期输出，应该对交易方法进行分组并显示单个结果

{
"Cash": 90,
"Debit Card": 50,
"EBT": 20,
"TotalExpense": 10
},

你可以在这里看到结果

Answer 1

您只需要将其附加到聚合函数的末尾：

(...)
{
"$project": {
"_id": 0.0,
"totalBalance": 0.0
}
},
{
$group: {
_id: "Total",
Cash: {
$sum: "$Cash"
},
"Debit Card": {
$sum: "$Debit Card"
},
EBT: {
$sum: "$EBT"
},
Expense: {
$sum: "$Expense"
},
TotalExpense: {
$sum: "$TotalExpense"
}
}
}