Add a total to aggregated sub-totals in MongoDB

Question

Let's say I have documents in my MongoDB collection that look like this:

{ name: "X", ...}
{ name: "Y", ...}
{ name: "X", ...}
{ name: "X", ...}

I can create a pipeline view using aggregation that shows sub-totals i.e.

$group: {
  _id: '$name',
  count: {
    $sum: 1
  }
}

which results in:

{ _id: "X",
  count: 3 },
{ _id: "Y",
  count: 1}

but how do I add a total in this view i.e.

{ _id: "X",
  count: 3 },
{ _id: "Y",
  count: 1},
{_id: "ALL",
 count: 4}

Answer 1

Query1

• group to count
• union with the same collection, with pipeline to add the total count, in one extra document

Test code here

coll1.aggregate(
[{"$group":{"_id":"$name", "count":{"$sum":1}}},
 {"$unionWith":
  {"coll":"coll1",
   "pipeline":[{"$group":{"_id":"ALL", "count":{"$sum":1}}}]}}])

Query2

• without $union for MongoDB < 4.4
• group and count
• group by null and collect the documents, and total count
• add to docs array the extra document with the total count
• unwind and replace root to restore the structure

Test code here

aggregate(
[{"$group":{"_id":"$name", "count":{"$sum":1}}},
 {"$group":
  {"_id":null, "docs":{"$push":"$$ROOT"}, "total":{"$sum":"$count"}}},
 {"$project":
  {"docs":
   {"$concatArrays":["$docs", [{"_id":"ALL", "count":"$total"}]]}}},
 {"$unwind":"$docs"},
 {"$replaceRoot":{"newRoot":"$docs"}}])