I have some documents like this:
{ "user": '1' }, { "user": '1' }, { "user": '2' }, { "user": '3' }I'd like to be able to get a set of all the different users and their respective counts, sorted in decreasing order. So my output would be something like this:
{ '1': 2, '2': 1, '3': 1 }I think this can be done with a Mongo aggregate(), but I'm having a lot of trouble figuring out the right flow for this.
asked Jan 18, 2015 at 18:36
3
You can get result (not in your required format) via aggregation
db.collection.aggregate( {$group : { _id : '$user', count : {$sum : 1}}} ).resultthe output for your sample documents is:
"0" : { "_id" : "2", "count" : 1 }, "1" : { "_id" : "3", "count" : 1 }, "2" : { "_id" : "1", "count" : 2 }answered Jan 18, 2015 at 18:47
DisposerDisposer
6,1014 gold badges29 silver badges38 bronze badges
3
For anyone reading this in Jan 2019 the accepted answer does not currently work in Robo3T (returns a pipeline.length - 1 error).
You must:
a) wrap the query in a set of square brackets []
b) remove .result from the end
//github.com/Studio3T/robomongo/issues/1519#issuecomment-441348191
Here's an update to the accepted answer by @disposer that works for me in Robo3T.
db.getCollection('collectionName').aggregate( [ {$group : { _id : '$user', count : {$sum : 1}}} ] )answered Jan 16, 2019 at 15:52
Ruben MurrayRuben Murray
1,07710 silver badges16 bronze badges
With MongoDb 3.6 and newer, you can leverage the use of $arrayToObject operator and a $replaceRoot pipeline to get the desired result. You would need to run the following aggregate pipeline:
db.collection.aggregate([ { "$group": { "_id": "$user", "count": { "$sum": 1 } } }, { "$sort": { "_id": 1 } }, { "$group": { "_id": null, "counts": { "$push": { "k": "$_id", "v": "$count" } } } }, { "$replaceRoot": { "newRoot": { "$arrayToObject": "$counts" } } } ])which yields
{ "1" : 2, "2" : 1, "3" : 1 }answered Sep 9, 2019 at 15:17
chridamchridam
97.1k21 gold badges223 silver badges227 bronze badges
You can use the below aggregation query, It will also sort the results in decreasing order as desired.
db.collection.aggregate([ { $group: { _id: "$user", count: { $sum: 1 } } }, { $sort: { count: -1 } } ])answered Jul 15, 2020 at 15:10
2
Docs Home → MongoDB Manual
Passes a document to the next stage that contains a count of the number of documents input to the stage.
Note
$count has the following prototype form:
<string> is the name of the output field which has the count as its value. <string> must be a non-empty string, must not start with $ and must not contain the . character.
The $count stage is equivalent to the following $group + $project sequence:
db.collection.aggregate( [ |
{ $group: { _id: null, myCount: { $sum: 1 } } }, |
{ $project: { _id: 0 } } |
] ) |
where myCount would be the output field that contains the count. You can specify another name for the output field.
Tip
See also:
A collection named scores has the following documents:
{ "_id" : 1, "subject" : "History", "score" : 88 } |
{ "_id" : 2, "subject" : "History", "score" : 92 } |
{ "_id" : 3, "subject" : "History", "score" : 97 } |
{ "_id" : 4, "subject" : "History", "score" : 71 } |
{ "_id" : 5, "subject" : "History", "score" : 79 } |
{ "_id" : 6, "subject" : "History", "score" : 83 } |
The following aggregation operation has two stages:
The $match stage excludes documents that have a score value of less than or equal to 80 to pass along the documents with score greater than 80 to the next stage.
The $count stage returns a count of the remaining documents in the aggregation pipeline and assigns the value to a field called passing_scores.
db.scores.aggregate( |
[ |
{ |
$match: { |
score: { |
$gt: 80 |
} |
} |
}, |
{ |
$count: "passing_scores" |
} |
] |
) |
The operation returns the following results: