multiple group in mongodb

Question

My collection look likes this.

{
"_id" : ObjectId("572c4ed33c1b5f51215219a8"),

"name" : "This is an angular course, and integeration with php",
"description" : "After we connected we can query or update the database just how we would using the mongo API with the exception that we use a callback. The format for callbacks is always callback(error, value) where error is null if no exception has occured. The update methods save, remove, update and findAndModify also pass the lastErrorObject as the last argument to the callback function.",
"difficulty_level" : "Beginner",
"type" : "Fast Track",
"tagged_skills" : [ 
    {
        "_id" : "5714e894e09a0f7d804b2254",
        "name" : "PHP"
    }, 
    {
        "_id" : "5717355806313b1f1715fa50",
        "name" : "c++"
    }, 
    {
        "_id" : "5715025bc2c5dbb4675180da",
        "name" : "java"
    }, 
    {
        "_id" : "5714f188ec325f5359979e33",
        "name" : "symphony"
    }
]}

I want to group by the collection on the basis of type,difficulty level and tagged skills and also get the count in a single query.

I am not been able to add skills count.

My query is as follows:-

db.course.aggregate([
{$unwind:"$tagged_skills"},
{$group:{
    _id:null,
skills: { $addToSet: "$tagged_skills.name" },
Normal_df:{$sum:{ 
                "$cond": [
                    { "$eq":[ "$difficulty_level","Normal"] },
                    1,
                    0
                ]
            }},
Beginner_df:{$sum:{ 
                "$cond": [
                    { "$eq":[ "$difficulty_level","Beginner"] },
                    1,
                    0
                ]
            }},
Intermediate_df:{$sum:{ 
                "$cond": [
                    { "$eq":[ "$difficulty_level","Intermediate"] },
                    1,
                    0
                ]
            }},
Advanced_df:{$sum:{ 
                "$cond": [
                    { "$eq":[ "$difficulty_level","Advanced"] },
                    1,
                    0
                ]
            }},
Fast_Track_type:{$sum:{ 
                "$cond": [
                    { "$eq":[ "$type","Fast Track"] },
                    1,
                    0
                ]
            }},
Normal_type:{$sum:{ 
                "$cond": [
                    { "$eq":[ "$type","Normal"] },
                    1,
                    0
                ]
            }},
Beginner_type:{$sum:{ 
                "$cond": [
                    { "$eq":[ "$type","Beginner"] },
                    1,
                    0
                ]
            }},
Normal_Track_type:{$sum:{ 
                "$cond": [
                    { "$eq":[ "$type","Normal Track"] },
                    1,
                    0
                ]
            }},
    }}
])

The result is as follows:-

{
    "_id" : null,

    "skills" : [ 
        "SQL", 
        "PHP", 
        "java", 
        "Angular Js", 
        "Laravel 23", 
        "c++", 
        "Node Js", 
        "symphony", 
        "Mysql", 
        "Express Js", 
        "JAVA"
    ],
    "Normal_df" : 1,
    "Beginner_df" : 14,
    "Intermediate_df" : 7,
    "Advanced_df" : 2,
    "Fast_Track_type" : 8,
    "Normal_type" : 6,
    "Beginner_type" : 1,
    "Normal_Track_type" : 9
}

I also want to get all skills with their count.

Answer 1

To get all the skills with their count, you need to first get a list of all the skills. You can obtain this list with running a distinct command on the approapriate fields. With this list you can then construct the appropriate $group pipeline document that will use the $sum and $cond operators.

Consider the following use case:

var difficultyLevels = db.course.distinct("difficulty_level"),
    types = db.course.distinct("type"),
    skills = db.course.distinct("tagged_skills.name"),
    unwindOperator = { "$unwind": "$tagged_skills" },
    groupOperator = { 
        "$group": { 
            "_id": null,
            "skills": { "$addToSet": "$tagged_skills.name" }
        }       
    };

difficultyLevels.forEach(function (df){ 
    groupOperator["$group"][df+"_df"] = { 
       "$sum": { 
           "$cond": [ { "$eq": ["$difficulty_level",  df] }, 1, 0] 
       }
   }
});

types.forEach(function (type){ 
    groupOperator["$group"][type.replace(" ", "_")+"_type"] = { 
       "$sum": { 
           "$cond": [ { "$eq": ["$type",  type] }, 1, 0] 
       }
   }
});

skills.forEach(function (skill){ 
    groupOperator["$group"][skill] = { 
       "$sum": { 
           "$cond": [ { "$eq": ["$tagged_skills.name",  skill] }, 1, 0] 
       }
   }
});

//printjson(groupOperator);
db.course.aggregate([unwindOperator, groupOperator]);

---

In the first line, we obtain an array with the difficulty levels by running the distinct command on the difficulty_level field

db.course.distinct("difficulty_level")

This will produce the array

var difficultyLevels = ["Normal", "Beginner", "Intermediate", "Advanced"]

Likewise, the preceding distinct operations will return the list of possible unique values for that key.

After getting these lists, you can then create the pipeline objects using the forEach() method to populate the document keys for each given item in the list. You can then use the resulting document, which will look like this

printjson(groupOperator);
{
    "$group" : {
        "_id" : null,
        "skills" : {
            "$addToSet" : "$tagged_skills.name"
        },
        "Beginner_df" : {
            "$sum" : {
                "$cond" : [
                    {
                        "$eq" : [
                            "$difficulty_level",
                            "Beginner"
                        ]
                    },
                    1,
                    0
                ]
            }
        },
        "Intermediate_df" : {
            "$sum" : {
                "$cond" : [
                    {
                        "$eq" : [
                            "$difficulty_level",
                            "Intermediate"
                        ]
                    },
                    1,
                    0
                ]
            }
        },
        "Fast_Track_type" : {
            "$sum" : {
                "$cond" : [
                    {
                        "$eq" : [
                            "$type",
                            "Fast Track"
                        ]
                    },
                    1,
                    0
                ]
            }
        },
        "PHP" : {
            "$sum" : {
                "$cond" : [
                    {
                        "$eq" : [
                            "$tagged_skills.name",
                            "PHP"
                        ]
                    },
                    1,
                    0
                ]
            }
        },
        "c++" : {
            "$sum" : {
                "$cond" : [
                    {
                        "$eq" : [
                            "$tagged_skills.name",
                            "c++"
                        ]
                    },
                    1,
                    0
                ]
            }
        },
        "java" : {
            "$sum" : {
                "$cond" : [
                    {
                        "$eq" : [
                            "$tagged_skills.name",
                            "java"
                        ]
                    },
                    1,
                    0
                ]
            }
        },
        "symphony" : {
            "$sum" : {
                "$cond" : [
                    {
                        "$eq" : [
                            "$tagged_skills.name",
                            "symphony"
                        ]
                    },
                    1,
                    0
                ]
            }
        },
        "C#" : {
            "$sum" : {
                "$cond" : [
                    {
                        "$eq" : [
                            "$tagged_skills.name",
                            "C#"
                        ]
                    },
                    1,
                    0
                ]
            }
        },
        "Scala" : {
            "$sum" : {
                "$cond" : [
                    {
                        "$eq" : [
                            "$tagged_skills.name",
                            "Scala"
                        ]
                    },
                    1,
                    0
                ]
            }
        },
        "javascript" : {
            "$sum" : {
                "$cond" : [
                    {
                        "$eq" : [
                            "$tagged_skills.name",
                            "javascript"
                        ]
                    },
                    1,
                    0
                ]
            }
        }
    }
}