Calculate distinct count on fields in mongodb

user_2136663 0
Viewed 13

I have following collection:

[{
    "id": 1,
    "activity_type": "view",
    "user_id": 1
},
{
    "id": 2,
    "activity_type": "save",
    "user_id": 1
},
{
    "id": 3,
    "activity_type": "save",
    "user_id": 1
},
{
    "id": 4,
    "activity_type": "save",
    "user_id": 2
}]

I need to get a result like this:

[{
   "activity_type": "view",
   "count": 1,
   "user_count": 1
},{
   "activity_type": "save",
   "count": 3,
   "user_count": 2
}]

So far I reached on this:

db.getCollection('activities').aggregate([
{
    $group:{_id:"$activity_type", count: {$sum: 1}}
},    
{
    $project: 
    {
        _id: 0,
        activity_type: "$_id",
        count: 1
    }
}
])

It gives me:

[{
    "activity_type": "view",
    "count": 1
},
{
    "activity_type": "save",
    "count": 3
}]

How can I add distinct user_id count as well?

mongotemplate mongodb
1 Answers

What you need to do is use $addToSet in the group stage to gather the unique ids, after that in the $project stage you can use $size to show the proper user count.

db.collection.aggregate([
  {
    $group: {
      _id: "$activity_type",
      count: {
        $sum: 1
      },
      user_ids: {
        "$addToSet": "$user_id"
      }
    }
  },
  {
    $project: {
      _id: 0,
      activity_type: "$_id",
      user_count: {
        $size: "$user_ids"
      },
      count: 1
    }
  }
])

Mongo Playground

user_107703700
Related