Reputation: 19705
In a pipeline, the following grouping
{
$group: {
"_id": "$salesperson",
"items": {
$push: "$city"
}
}
}
produces results like below:
{
"_id":"Fred",
"items": ["Boston", "Chicago", "Chicago", "Boston"]
},
{
"_id":"Mary",
"items": ["Austin", "Chicago", "Austin", "Louisville"]
}
I want to count distinct cities for each salesperson so that I would get this:
[
{
"Fred":2
},
{
"Mary":3
}
]
How can the pipeline be modified to achieve that?
Edit: After using $addToSet
as recommended, I get a pipeline like the following:
[
{
"$match": {
"$and": [
{
"field1": {
"$in": ["a", "b", "c"]
}
},
{
"field2": {
"$in": ["d", "e"]
}
}
]
}
},
{
"$group": {
"_id": "$salesperson",
"items": {
"$addToSet": "$city"
}
}
}
]
How can I count items in each set, per group?
Upvotes: 2
Views: 262
Reputation: 1352
You are going in good direction, just instead of $push (which add the field in an array), you need to use $addToSet (which will add unique fields in the array).
{
$group: {
"_id": "$salesperson",
"items": {
$addToSet: "$city"
}
}
}
now it will produce the result:
{
"_id":"Fred",
"items": ["Boston", "Chicago"]
},
{
"_id":"Mary",
"items": ["Austin", "Chicago", "Louisville"]
}
and in the next stage, you can count the elements in the array like you are doing already
Here I'm adding the complete query:
db.collection.aggregate([
{
$group: {
"_id": "$salesperson",
"items": {
$addToSet: "$city"
}
}
},
{
$group:{
"_id":null,
"data":{
$push:{
"k":"$_id",
"v":{
$size:"$items"
}
}
}
}
},
{
$project:{
"data":{
$arrayToObject:"$data"
}
}
},
{
$replaceRoot:{
"newRoot":"$data"
}
}
]).pretty()
And it will give you the output:
{ "Fred" : 2, "Mary" : 3 }
Hope it will help you. And for more refer $addToSet.
Upvotes: 4