CODEWITHSUNDEEP
mongodbaggregation-framework
Joshua Fox
Joshua Fox

Reputation: 19705

In Mongodb, how do I aggregate to count distinct items per-group?

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

Answers (1)

Vijay Rajpurohit
Vijay Rajpurohit

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

Related Questions