abhilashak
abhilashak

Reputation: 3621

Mongodb 3.4.7 aggregation distinct count of one field after grouping

I have

{
"statement": {
   “activity”: {
    "definition": {
             “name”: “ACT_NAME1”
    }
    },
"actor": {
       "name": "User 2”
    }
},
"statement": {
   “activity”: {
    "definition": {
             “name”: “ACT_NAME2”
    }
    },
"actor": {
       "name": "User 1"
    }
},
"statement": {
   “activity”: {
    "definition": {
             “name”: “ACT_NAME2”
    }
    },
"actor": {
       "name": "User 1"
    }
},
"statement": {
   “activity”: {
    "definition": {
             “name”: “ACT_NAME1”
    }
    },
"actor": {
       "name": "User 1"
    }
 }
}

I want to group by statement.activity.definition.name and in each group calculate distinct count of actor.name. How to do this?

{ activity: ACT_NAME1
  unique_actor_count: 2 }

{ activity: ACT_NAME2
 unique_actor_count: 1 }

Also I have some match condition for this and not provided here.

Upvotes: 0

Views: 67

Answers (1)

Mạnh Quyết Nguyễn
Mạnh Quyết Nguyễn

Reputation: 18235

Use $addToSet to accumulate grouped items into a Set (unique elements)

document.collection.aggregate([
    {$match: {}},
    {$group: 
         {
            _id: '$statement.activity.definition.name',
            actors: {$addToSet: '$statement.actor.name'}
         }
     },
     {$project: {
         _id: '$_id',
         actors: '$actors',
         uniqueActorsCount: {$size: '$actors'}
     }}
])

Upvotes: 1

Related Questions