CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
Alejandro Montilla
Alejandro Montilla

Reputation: 2654

Add to collection one document that is one field's accumulation in MongoDB

How can I add to a collection one document that accumulate the value collection's field in MongoDB?

I have a MongoDB collection with documents in the following format:

{
  "_id" : 3876435465554,
  "title" : "xxx",
  "category" : "xxx",
  ...
}

So the desire result would be:

{ "_id" : "All", "num" : 28 }  // <- This is the document that I want include in the output
{ "_id" : "xxx", "num" : 11 }
{ "_id" : "yyy", "num" : 8 }
{ "_id" : "zzz", "num" : 9 }

So far I've tried this:

db.collection.aggregate([
  { $project: { title:1, category:1} },
  { $group: {
      _id: { _id:"$category"},
      num: { $sum: 1 }
    } },
  { $project: { _id:"$_id._id", num:1} },
  { $sort: { _id:1} }
])

But that produce just the documents without the "All" document:

{ "_id" : "xxx", "num" : 11 }
{ "_id" : "yyy", "num" : 8 }
{ "_id" : "zzz", "num" : 9 }

I dont know how to add the "All" document with the sum of all "num" values.

Note: Chaining 2 calls to aggregates I was able to get the desire output in the program, but the idea is to get the output with just one aggregate.

Upvotes: 0

Views: 248

Answers (2)

s7vr
s7vr

Reputation: 75984

You can use below aggregation query in 3.4.

Changes include adding extra $group to calculate total count while pushing the individual count rows into array followed by $concatArrays to add the total row document to individual count array.

$unwind to go back to flat structure and $sort by _id and $replaceRoot to promote all the docs to top level.

db.collection.aggregate([
  {"$project":{"title":1,"category":1}},
  {"$group":{"_id":{"_id":"$category"},"num":{"$sum":1}}},
  {"$group":{
    "_id":null,
    "total":{"$sum":"$num"},
    "rest":{"$push":{"num":"$num","_id":"$_id._id"}}
  }},
  {"$project":{"data":{"$concatArrays":[[{"_id":"all","num":"$total"}],"$rest"]}}},
  {"$unwind":"$data"},
  {"$sort":{"data._id":1}},
  {"$replaceRoot":{"newRoot":"$data"}}
])

Upvotes: 1

Alex Blex
Alex Blex

Reputation: 37058

You can "chain" the pipelines on the db side using $facets, so it will be single request from the client:

db.collection.aggregate([
  { $match: { category: { $ne: 'all' } } },
  { $project: { title:1, category:1 } },
  { $facet: {
      total: [ 
        { $group: {
          _id: 'all', 
          num: { $sum: 1 }
         } },
      ],
      categories: [   
        { $group: {
          _id: "$category",
          num: { $sum: 1 }
         } },
      ]
  } },
  { $project: { all: { $concatArrays: [ "$total", "$categories" ] } } },
  { $unwind: "$all" },
  { $replaceRoot: { newRoot: "$all" } },  
  { $sort: { _id:1 } }
])

Upvotes: 1

Related Questions