MongoDB get sum of fields in last stage of aggregate

I’m using an aggregate to gather some related data. The following is a sample pipeline that joins a Parent Collection to a Children One (Each Parent will have a Child).

I then use a projection to gather some fields from both collections. This includes getting a count of the “number of sodas” a child has.

Ultimately, I’m aiming to get a total of all the sodas that all children have (So basically a summation of the childSodaCount field in the projection.

I attempted this by appending a group stage at the end of the pipeline. THis does work, however, I lose all the other fields from the projection.

Any insights?

[
  {
    '$lookup': {
      'from': 'Children', 
      'localField': 'childId', 
      'foreignField': '_id', 
      'as': 'CHILDREN'
    }
  } {
    '$unwind': {
      'path': '$CHILDREN' 
    }
  } {
    '$project': {
      'childSodaCount': {
        '$size': '$CHILDREN.sodas'
      }, '
      'parentName': 1, 
      'parentFoo': 1, 
      'parentBar': 1, 
     
      'childName': {
        '$concat': [
          '$CHILDREN.firstName', ' ', '$CHILDREN.lastName'
        ]
      }, 
    {
    '$group': {
      '_id': null, 
      'TOTAL_CHILD_SODA_COUNT': {
        '$sum': '$childSodaCount'
      }
    }
  }
]

Answer

Basically $group by null will group all documents in a single document, Try $facet to separate both result after $project stage,

{
  $facet: {

    // All documents
    docs: [ { $match: {} } ],

    // Total child soda
    TOTAL_CHILD_SODA_COUNT: [
      {
        $group: {
          _id: null,
          count: {
            $sum: "$childSodaCount"
          }
        }
      }
    ]

  }
}

This will result something like,

[
  {
    docs: [{}, {}, .. all docs]
    TOTAL_CHILD_SODA_COUNT: [{ _id: null, count: 1 }] // total count
  }
]

If you want to get direct count instead of array of object try $addFields stage after $facet stage,

{
  $addFields: {
    TOTAL_CHILD_SODA_COUNT: { $arrayElemAt: ["$TOTAL_CHILD_SODA_COUNT.count", 0] }
  }
}

Your final result will be,

[
  {
    docs: [{}, {}, .. all docs]
    TOTAL_CHILD_SODA_COUNT: 1 // total count
  }
]