MongoDB group and sum with id as key

Question

Is it possible to get the result of an aggregate function as key:count ?

Example:

I have the following aggregate query:

 db.users.aggregate([ { $group: { _id: "$role", count: { $sum: 1 } } } ]) 

so the results look like this:

 { "_id" : "moderator", "count" : 469 } { "_id" : "superadmin", "count" : 1 } { "_id" : "user", "count" : 2238 } { "_id" : "admin", "count" : 11 } 

So, everything is fine, but is there a way (possibly using $project ) to make the results look like this (i.e. with role as the key and count as the value):

 { "moderator": 469 } { "superadmin": 1 } { "user": 2238 } { "admin": 11 } 

I could do this, obviously, after processing the result using JS, but my goal is to do this directly through the aggregate function.

Answer 1

With MongoDb 3.6 and later, you can use $arrayToObject and $replaceRoot to get the desired result. You will need to run the following aggregate conveyor:

 db.users.aggregate([ { "$group": { "_id": { "$toLower": "$role" }, "count": { "$sum": 1 } } }, { "$group": { "_id": null, "counts": { "$push": { "k": "$_id", "v": "$count" } } } }, { "$replaceRoot": { "newRoot": { "$arrayToObject": "$counts" } } } ]) 

---

For older versions, the $cond operator in the $group step step can be effectively used to evaluate counts based on the role field value. Your general aggregation pipeline can be built as follows to get the result in the desired format:

 db.users.aggregate([ { "$group": { "_id": null, "moderator_count": { "$sum": { "$cond": [ { "$eq": [ "$role", "moderator" ] }, 1, 0 ] } }, "superadmin_count": { "$sum": { "$cond": [ { "$eq": [ "$role", "superadmin" ] }, 1, 0 ] } }, "user_count": { "$sum": { "$cond": [ { "$eq": [ "$role", "user" ] }, 1, 0 ] } }, "admin_count": { "$sum": { "$cond": [ { "$eq": [ "$role", "admin" ] }, 1, 0 ] } } } }, { "$project": { "_id": 0, "moderator": "$moderator_count", "superadmin": "$superadmin_count", "user": "$user_count", "admin": "$admin_count" } } ]) 

---

From the comment trend, if you do not know the role in advance and want to dynamically create a pipeline array, run distinct in the roles field. This will give you an object that contains a list of individual roles:

 var result = db.runCommand ( { distinct: "users", key: "role" } ) var roles = result.values; printjson(roles); // this will print ["moderator", "superadmin", "user", "admin"] 

Now, given the list above, you can build your pipeline by creating an object that will have its properties set using JavaScript reduce() . This demonstrates the following:

 var groupObj = { "_id": null }, projectObj = { "_id": 0 } var groupPipeline = roles.reduce(function(obj, role) { // set the group pipeline object obj[role + "_count"] = { "$sum": { "$cond": [ { "$eq": [ "$role", role ] }, 1, 0 ] } }; return obj; }, groupObj ); var projectPipeline = roles.reduce(function(obj, role) { // set the project pipeline object obj[role] = "$" + role + "_count"; return obj; }, projectObj ); 

Use these two documents in your final aggregation pipeline as:

 db.users.aggregate([groupPipeline, projectPipeline]); 

Check out the demo below.

 var roles = ["moderator", "superadmin", "user", "admin"], groupObj = { "_id": null }, projectObj = { "_id": 0 }; var groupPipeline = roles.reduce(function(obj, role) { // set the group pipeline object obj[role + "_count"] = { "$sum": { "$cond": [ { "$eq": [ "$role", role ] }, 1, 0 ] } }; return obj; }, groupObj ); var projectPipeline = roles.reduce(function(obj, role) { // set the project pipeline object obj[role] = "$" + role + "_count"; return obj; }, projectObj ); var pipeline = [groupPipeline, projectPipeline] pre.innerHTML = JSON.stringify(pipeline, null, 4); 

 <pre id="pre"></pre> 

Answer 2

You can use map-reduce:

 db.users.mapReduce( function map() { emit(this.role, 1); }, function reduce(key, values) { var sum = 0; for (var i = 0; i < values.length; i++) { sum += i; } return sum; }, { out: { inline: 1 }, finalize: function (key, reducedValue) { var obj = {}; obj[key] = reducedValue; return obj; } } ) 

and display only value