CODEWITHSUNDEEP
mongodb
Steven
Steven

Reputation: 13995

MongoDB SELECT field, COUNT GROUP

I am trying to do something like

SELECT province, COUNT(distinct id) FROM user GROUP BY province

So get something like

province -> entries (unique to id)

But it seems to be proving to be really difficult. I ended up with

db.user.aggregate([
    { $group: { _id: "$id"}  },
    { $group: { _id: "$province", count: { $sum: 1 } } } 
])

But that's giving me the total number of distinct ID values, not grouped by province. How can I properly translate that query over to the aggregate function?

Example documents.

{
    "_id" : ObjectId("534fd9f7b40801d50b8b4567"),
    "id" : "4",
    "province" : "on",
}

{
    "_id" : ObjectId("534fd9f7b40801d50b8b4567"),
    "id" : "4",
    "province" : "on",
}

Should return

on - 1

Because there is two rows with the same "id", so we only add 1 to on.

Upvotes: 0

Views: 1901

Answers (2)

JohnnyHK
JohnnyHK

Reputation: 312129

First group on province and id to get the unique combinations, and then group on just province to get the count of ids per province:

db.test.aggregate([
    {$group: {_id: {province: '$province', id: '$id'}}},
    {$group: {_id: '$_id.province', count: {$sum: 1}}}
])

gives a result of:

{
    "result" : [ 
        {
            "_id" : "on",
            "count" : 1
        }
    ],
    "ok" : 1
}

Upvotes: 1

Anand Jayabalan
Anand Jayabalan

Reputation: 12944

One approach to get the results you want would be to use the $addToSet operator, which is used to add only unique values to an array. The complete query would look like:

db.user.aggregate([
    // Group by "province" and add unique values of "id" to array
    { $group: { 
        _id: "$province", 
        id: { $addToSet: "$id" } 
    }}, 
    // De-normalize the array into separate documents
    {$unwind:"$id"}, 
    // Get the distinct count
    {$group:{_id:"$_id", count:{$sum:1}}}
])

If you are using MongoDB 2.6, you can also use the $size aggregation operator to get the size of the id array.

Upvotes: 0

Related Questions