Mongo count occurrences of each value for a set of documents

Question

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.

it is easy to get answer via aggregation (output will be): "0" : { "_id" : "2", "count" : 1 }, "1" : { "_id" : "3", "count" : 1 }, "2" : { "_id" : "1", "count" : 2 } — Disposer, Jan 18 '15 at 18:41
How would you count the number of occurrences for each field? — ritmatter, Jan 18 '15 at 18:45
@ritmatter Added an answer for the same using two step aggregation. — Rajdeep Gautam, Jul 15 '20 at 15:12

score 94 · Accepted Answer · answered Jan 18 '15 at 18:47

94

You can get result (not in your required format) via aggregation

db.collection.aggregate(
   {$group : { _id : '$user', count : {$sum : 1}}}
).result

the output for your sample documents is:

"0" : {
    "_id" : "2",
    "count" : 1
},
"1" : {
    "_id" : "3",
    "count" : 1
},
"2" : {
    "_id" : "1",
    "count" : 2
}

answered Jan 18 '15 at 18:47

Disposer

6,201
4
31
38

6

Worked for me, but I had to remove .result. I'm running it from Robomongo like `db.getCollection('MyColection').aggregate({$group : { _id : '$post.source', cant: {$sum : 1}}})` – Rodrigo Laguna Apr 13 '18 at 18:38
@RodrigoLaguna Your code doesn't make any sense. Did you just copy and past it from your own project?? – Vahid Amiri Jul 09 '18 at 06:09
1

Yes, it is almost the same except for cant instead of count. I thought it was easy enough to map it to other examples so I let it as it was. – Rodrigo Laguna Jul 09 '18 at 14:52

score 41 · Answer 2 · answered Jan 16 '19 at 15:52

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

https://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}}} ]
)

score 11 · Answer 3 · answered Sep 09 '19 at 15:17

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
}

score 5 · Answer 4 · answered Jul 15 '20 at 15:10

5

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 '20 at 15:10

Rajdeep Gautam

642
9
15

1

Sorry, that answer has incorrect notation. – Ilya Pukhov Oct 12 '21 at 06:23
@IlyaPukhov indeed, it should be: db.collection.aggregate([ { $group: { _id: "$user", count: { $sum: 1 } } }, { $sort: { count: -1 } ]) – Tom O Apr 19 '22 at 07:18

Mongo count occurrences of each value for a set of documents

4 Answers4

Linked