MongoDB aggregate to get stats-CodePudding

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"
      }
    }
  }
])

Demo