MongoDB Aggregation: Counting distinct fields

Question

I am trying to write an aggregation to identify accounts that use multiple payment sources. Typical data would be.

{  account:"abc",  vendor:"amazon", }  ... {  account:"abc",  vendor:"overstock", } 

Now, I'd like to produce a list of accounts similar to this

{  account:"abc",  vendorCount:2 } 

How would I write this in Mongo's aggregation framework

Answer 1

I figured this out by using the $addToSet and $unwind operators.

Mongodb Aggregation count array/set size

db.collection.aggregate([ {     $group: { _id: { account: '$account' }, vendors: { $addToSet: '$vendor'} } }, {     $unwind:"$vendors" }, {     $group: { _id: "$_id", vendorCount: { $sum:1} } } ]); 

Hope it helps someone

Answer 2

I think its better if you execute query like following which will avoid unwind

db.t2.insert({_id:1,account:"abc",vendor:"amazon"}); db.t2.insert({_id:2,account:"abc",vendor:"overstock"});   db.t2.aggregate( { $group : { _id : { "account" : "$account", "vendor" : "$vendor" }, number : { $sum : 1 } } }, { $group : { _id : "$_id.account", number : { $sum : 1 } } } ); 

Which will show you following result which is expected.

{ "_id" : "abc", "number" : 2 }