Mongoose multiple sort and get sum-CodePudding

The collection structure is:

  {
    "_id": 1, <-object id
    "moneyAccount": ObjectId("635501bec5f83658d5542d33"), <-object id
    "operationPaid": true, <- boolean
    "sum": 149, <- number
    "type": "coming", <- string
    "shiftId": <- object id
  },

I need to get all records where shiftId is ObjectId("999999999999999999999999"), group them by type field and get sum by field operationPaid

the result will be like this:

{
    "moneyAccount": ObjectId("635501bec5f83658d5542d33"),
    "coming": {
        paid: 349,
        nPaid: 2
    },
    "out": {
        paid: 77,
        nPaid: 32
    },
    "shiftId": ObjectId("999999999999999999999999")
 },
 {
    "moneyAccount": ObjectId("652501bec5f38658d5542d55"),
    "coming": {
        paid: 0,
        nPaid: 2
    },
    "out": {
        paid: 0,
        nPaid: 0
    },
    "shiftId": ObjectId("999999999999999999999999")
 },

Mongo Playground: https://mongoplayground.net/p/hn9gvUVHBUN

CodePudding user response：

You can try,

$match to check shiftId condition
$group by type and moneyAccount and get the sum of paid and not paid amounts
$group by moneyAccount and construct the array of both the sum of the amount type-wise and in key and value format
$arrayToObject to convert above-constructed array to an object
$mergeObjects to merge the above-converted object and required properties
$replaceRoot to replace the above object with the root

db.collection.aggregate([
  { $match: { shiftId: ObjectId("777777777777777777777777") } },
  {
    $group: {
      _id: {
        type: "$type",
        moneyAccount: "$moneyAccount"
      },
      paid: {
        $sum: { $cond: ["$operationPaid", "$sum", 0] }
      },
      nPaid: {
        $sum: { $cond: ["$operationPaid", 0, "$sum"] }
      }
    }
  },
  {
    $group: {
      _id: "$_id.moneyAccount",
      type: {
        $push: {
          k: "$_id.type",
          v: { paid: "$paid", nPaid: "$nPaid" }
        }
      }
    }
  },
  {
    $replaceRoot: {
      newRoot: {
        $mergeObjects: [
          { moneyAccount: "$_id" },
          { $arrayToObject: "$type" }
        ]
      }
    }
  }
])

Playground