I have an array of objects and need to include the grand total of values in a field the pipeline output.

[
  {
    "_id": NumberInt(1),
    "absences": 170.0,
    "dayNumber": NumberInt(1),
    "dayName": "Sunday"
  },
  {
    "_id": NumberInt(2),
    "absences": 130.0,
    "dayNumber": NumberInt(2),
    "dayName": "Monday"
  }
]

My desired outcome includes the totalAbsences key, which sums absences from all documents.

[
  {
    "_id": 1,
    "absences": 170,
    "dayName": "Sunday",
    "dayNumber": 1,
    "totalAbsences": 300
  },
  {
    "_id": 2,
    "absences": 130,
    "dayName": "Monday",
    "dayNumber": 2,
    "totalAbsences": 300
  }
]

I can get the grand total with the code below, but I lose all other values in the pipeline. How can I add totalAbsences to every document and preserve what's already in the pipeline? This Mongo Playground has a mock of the data.

{
    _id: null,
        total: { 
            $sum: "$absences" 
        } 
}

CodePudding user response：

db.collection.aggregate([
  {
    $group: {
      _id: null,
      total: {
        $sum: "$absences"
      },
      items: {
        $push: "$$ROOT"
      }
    }
  },
  {
    $unwind: "$items"
  },
  {
    $replaceRoot: {
      "newRoot": {
        $mergeObjects: [
          {
            total: "$total"
          },
          "$items"
        ]
      }
    }
  }
])

mongoplayground

CodePudding user response：

In MongoDB version 5.0 you can use new $setWindowFields(), which is much simpler and most likely also faster:

db.collection.aggregate([
  {
    $setWindowFields: {
      output: {
        totalAbsences: { $sum: "$absences" }
      }
    }
  }
])

CodePudding user response：

You can use $first with $$ROOT to keep document.

{
  _id: '$orgId',
  total: {
    $sum: '$createdAt'
  },
  "doc": { "$first": "$$ROOT" }
}

After that you can use $replaceRoot and $mergeObjects.

{
  newRoot: { $mergeObjects : [{ total: '$total' }, '$doc']}
}