Hatshepsut February 2016

Group by one field and count distinct values of another

There are a number of questions on SO that are like this one, but I'm not sure which (if any) are the same as my question.

I have a collection

{name: 'amy', age:19}
{name: 'bob', age:80}
{name: 'bob', age:80}
{name: 'chris', age:31}
{name: 'chris', age:20}

and I would like to get the number of distinct age values for each name. So I'd like to return

{'amy': 1, 'bob': 1, 'chris': 2}

I've been trying aggregate queries with group and sum, but I can't quite get it right.

Answers


BatScream February 2016

You can aggregate as below, the key is to use: $addToSet operator, to accumulate distinct ages, and then use the $size operator to get the number of distinct ages, per name(group).

db.t.aggregate([
{$group:{"_id":"$name","ages":{$addToSet:"$age"}}},
{$project:{"name":"$_id","_id":0,"count":{$size:"$ages"}}}
])

Post Status

Asked in February 2016
Viewed 1,538 times
Voted 4
Answered 1 times

Search




Leave an answer