I've done this sometime last year, but now I really can't recall and can't find any helpful resources. I want to get the statistics of my collection based on types.
This is my data object
{
"_id": {
"$oid": "63bfc374378c59a5328f229e"
},
"amountEarned": 11500,
"amountPaid": 10350,
"relianceCommission": 1150,
"receiverType": "RESTAURANT",
"__v": 0
}
I just need the sum of amountPaid
for each receiverType
, it could be STORE
, RESTAURANT
or SHOPPER
. Then I also need the sum of relianceCommission
for all. Resulting in a shape like
{
storeEarnings: 500,
restaurantEarnings: 30,
shopperEarnings: 40,
totalRelianceCommission: 45
}
I've tried
aggregate([
{
$group: {_id: "$receiverType", total: {$sum: "amountPaid"}}
}
])
And then joining with another pipeline to calculate totalRelianceCommission
, but I feel there should be a neater way to do it. I'm also not sure how to do the projections to result in the desired shape. Please help.
CodePudding user response:
query:
{
$group: {
_id: "$receiverType",
total: {
$sum: "$amountPaid"
},
commissions: {
$sum: "$relianceCommission"
}
}
}
result:[
{
"_id": "STORE",
"commissions": 1150,
"total": 10350
},
{
"_id": "RESTAURANT",
"commissions": 2300,
"total": 20700
}
]
loop through the array to get a sum of commissions
CodePudding user response:
You need conditional sum.
db.collection.aggregate([
{
$group: {
_id: null,
storeEarnings: {
$sum: {
$cond: [{$eq: ["$receiverType","STORE"]},"$amountPaid",0]
}
},
restaurantEarnings: {
$sum: {
$cond: [{$eq: ["$receiverType","RESTAURANT"]},"$amountPaid",0]
}
},
shopperEarnings: {
$sum: {
$cond: [{$eq: ["$receiverType","SHOPPER"]},"$amountPaid",0]
}
},
totalRelianceCommission: {
$sum: "$relianceCommission"
}
}
}
])