CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
fadeltd
fadeltd

Reputation: 349

Mongo Group By Condition and Count

I have a data like this, what I'm trying to do is count the employee by grouping them with gender.

{
   _id: ObjectId("57fd6b064a8d7733079d1bb9"),
   name: "Alex",
   age: 23,
   position: "Manager",
   employees: [{
       _id: ObjectId("58056e62281b7a73dfdb6887"),
       employee_id: ObjectId("57df832364efef57c3540610"),
       name: "Bob",
       age: 20,
       gender: "Male",
       position: "Distributor"
   }, {
       _id: ObjectId("58049fe7bc82e44583c52a64"),
       employee_id: ObjectId("57df830264efef57c354060d"),
       name: "Cindy",
       age: 19,
       gender: "Female",
       position: "Administrator"
   }, {
       _id: ObjectId("58049fe7bc82e44583c52a64"), 
       employee_id: ObjectId("57df830264efef57c354060d"), 
       name: "Dylan",
       age: 21,
       gender: "Male",
       position: "Engineer"
  }]
}

So, the data I'd like to have is like this

{
   _id: ObjectId("57fd6b064a8d7733079d1bb9"),
   name: "Alex",
   age: 23,
   position: "Manager",
   male_employees: 2,
   female_employees: 1,
}

I've tried aggregate but still nothing close to what I want to have. I have no idea how to group them and use size at the same time

Upvotes: 2

Views: 1876

Answers (1)

chridam
chridam

Reputation: 103305

In your aggregation pipeline, you don't need to group the documents but just project the fields you want using a single $project pipeline and with that you'd need to use a $size and $filter combo to get the number of employees based on gender without using $unwind.

The $filter part of the equation will filter out the array elements based on a given condition, which in this case would be the binary gender values. The $size operator will then return the length of the filtered array thus giving you a count of the employees.

Let's see this with an example:

var count = function(gender){
    return {
        "$size": {
            "$filter": {
                "input": "$employees",
                "as": "emp",
                "cond": { "$eq": [ "$$emp.gender", gender ] }
            }
        }
    }
};
db.collection.aggregate([
    {
        "$project": {
            "name": 1,
            "age": 1,
            "position": 1,
            "male_employees": count("Male"),
            "female_employees": count("Female")
        }
    }
]);

Sample Output

{
    "_id" : ObjectId("57fd6b064a8d7733079d1bb9"),
    "name" : "Alex",
    "age" : 23,
    "position" : "Manager",
    "male_employees" : 2,
    "female_employees" : 1
}

Upvotes: 4

Related Questions