Sunday, March 18, 2018

Mongo Agreegation

 Here we will explore group by clause in mongo.

Inserting Sample Data:
    Query:
        db.studentinfo.insertMany([
            {"name":"sagar","class":10,"marathi":80,"english":75,"history":68,"geography":78,"hindi":67,"math":76,"science1":67,"science2":70,"region":"rural","school":"A"},
            {"name":"sangram","class":10,"marathi":81,"english":72,"history":60,"geography":72,"hindi":62,"math":70,"science1":68,"science2":72,"region":"rural","school":"A"},
            {"name":"sachin","class":10,"marathi":69,"english":62,"history":80,"geography":82,"hindi":72,"math":70,"science1":78,"science2":70,"region":"urban","school":"B"},
            {"name":"swapnil","class":10,"marathi":60,"english":60,"history":80,"geography":72,"hindi":82,"math":60,"science1":68,"science2":80,"region":"urban","school":"C" },
            {"name":"rock","class":10,"marathi":81,"english":72,"history":70,"geography":72,"hindi":62,"math":74,"science1":68,"science2":69,"region":"rural","school":"A"},
            {"name":"mary","class":10,"marathi":67,"english":71,"history":80,"geography":82,"hindi":72,"math":69,"science1":78,"science2":84,"region":"urban","school":"B"},
            ]);
    output:
        {
            "acknowledged" : true,
            "insertedIds" : [
                ObjectId("5aa4b8fae7232cc5677a7738"),
                ObjectId("5aa4b8fae7232cc5677a7739"),
                ObjectId("5aa4b8fae7232cc5677a773a"),
                ObjectId("5aa4b8fae7232cc5677a773b")
            ]
        }

Sum:
    db.studentinfo.aggregate([
             { $match: { class: 10 } },
             { $group: { _id: "$region", total: { $sum: "$marathi" } } },
           ])

Group by Multiple Columns:

    db.studentinfo.aggregate([
                 { $match: { class: 10 } },
                     { $group: { _id: {"region": "$region","class": "$class"}, total: { $sum: "$marathi" } } },
                   ])

    db.studentinfo.aggregate([
                 { $match: { class: 10 } },
                     { $group: { _id: {"region": "$region","school": "$school"}, marathi: { $sum: "$marathi" }, hindi: { $sum: "$hindi" } , english: { $sum: "$english" }} },
                   ])


    db.studentinfo.aggregate([
                 { $match: { class: 10,region:"rural" } },
                     { $group: { _id: {"region": "$region","school": "$school"}, marathi: { $sum: "$marathi" }, hindi: { $sum: "$hindi" } , english: { $sum: "$english" }} },
                   ])

Projection:

    db.studentinfo.aggregate([
                 { $match: { class: 10,region:"rural" } },
                     { $group: { _id: {"region": "$region","school": "$school"}, marathi: { $sum: "$marathi" }, hindi: { $sum: "$hindi" } , english: { $sum: "$english" }} },
                 {$project : {region : '$_id.region', school : '$_id.school', marathi : '$marathi',hindi : '$hindi',english : '$english', _id : 0}}
                   ])
    db.studentinfo.aggregate([
                 { $match: { class: 10} },
                     { $group: { _id: {"region": "$region","school": "$school"}, marathi: { $sum: "$marathi" }, hindi: { $sum: "$hindi" } , english: { $sum: "$english" }} },
                 {$project : {region : '$_id.region', school : '$_id.school', marathi : '$marathi',hindi : '$hindi',english : '$english', _id : 0}}
                   ])
Sorting:

    db.studentinfo.aggregate([
                 { $match: { class: 10} },
                     { $group: { _id: {"region": "$region","school": "$school"}, marathi: { $sum: "$marathi" }, hindi: { $sum: "$hindi" } , english: { $sum: "$english" }} },
                 {$project : {region : '$_id.region', school : '$_id.school', marathi : '$marathi',hindi : '$hindi',english : '$english', _id : 0}},
                 {$sort:{"region":1,"school":1}}
                   ])

    db.studentinfo.aggregate([
                 { $match: { class: 10} },
                     { $group: { _id: {"region": "$region","school": "$school"}, marathi: { $avg: "$marathi" }, hindi: { $avg: "$hindi" } , english: { $avg: "$english" }} },
                 {$project : {region : '$_id.region', school : '$_id.school', marathi : '$marathi',hindi : '$hindi',english : '$english', _id : 0}},
                 {$sort:{"region":1,"school":1}}
                   ])

Making Sum of two column in mongo

Query:
    db.studentinfo.aggregate([
            { "$project" : {
                    'name':'$name',
                    'socialscience' : { '$add' : [ '$history','$geography' ] },
                    'science' : { '$add' : [ '$science1','$science2' ] },
                    'language' : { '$add' : [ '$marathi', '$hindi','$english' ] },
                }
            }
              ])
Output:
    { "_id" : ObjectId("5aa4bbe212899b0fde851291"), "name" : "sagar", "socialscience" : 146, "science" : 137, "language" : 222 }
    { "_id" : ObjectId("5aa4bbe212899b0fde851292"), "name" : "sangram", "socialscience" : 132, "science" : 140, "language" : 215 }
    { "_id" : ObjectId("5aa4bbe212899b0fde851293"), "name" : "sachin", "socialscience" : 162, "science" : 148, "language" : 203 }
    { "_id" : ObjectId("5aa4bbe212899b0fde851294"), "name" : "swapnil", "socialscience" : 152, "science" : 148, "language" : 202 }
    { "_id" : ObjectId("5aa4bbe212899b0fde851295"), "name" : "rock", "socialscience" : 142, "science" : 137, "language" : 215 }
    { "_id" : ObjectId("5aa4bbe212899b0fde851296"), "name" : "mary", "socialscience" : 162, "science" : 162, "language" : 210 }

Query:
    db.studentinfo.aggregate([
            { "$project" : {
                    'name':'$name',
                    'class':'$class',
                    'region':'$region',
                                'school':'school',
                    'total_socialscience' : { '$add' : [ '$history','$geography' ] },
                    'total_science' : { '$add' : [ '$science1','$science2' ] },
                    'total_language' : { '$add' : [ '$marathi', '$hindi','$english' ] },

                                'avg_science':{ $divide: [ { '$add' : [ '$science1','$science2' ] }, 2 ] },
                                'avg_socialscience':{ $divide: [ { '$add' : [ '$history','$geography' ] }, 2 ] },
                                'avg_language':{ $divide: [ { '$add' : [ '$marathi', '$hindi','$english' ] }, 3 ] },
                }
            }
              ])

Output:
        { "_id" : ObjectId("5aa4bbe212899b0fde851291"), "name" : "sagar", "class" : 10, "region" : "rural", "school" : "school", "total_socialscience" : 146, "total_science" : 137,
        "total_language" : 222, "avg_science" : 68.5, "avg_socialscience" : 73, "avg_language" : 74 }
        { "_id" : ObjectId("5aa4bbe212899b0fde851292"), "name" : "sangram", "class" : 10, "region" : "rural", "school" : "school", "total_socialscience" : 132, "total_science" : 140,
        "total_language" : 215, "avg_science" : 70, "avg_socialscience" : 66, "avg_language" : 71.66666666666667 }
        { "_id" : ObjectId("5aa4bbe212899b0fde851293"), "name" : "sachin", "class" : 10, "region" : "urban", "school" : "school", "total_socialscience" : 162, "total_science" : 148,
        "total_language" : 203, "avg_science" : 74, "avg_socialscience" : 81, "avg_language" : 67.66666666666667 }
        { "_id" : ObjectId("5aa4bbe212899b0fde851294"), "name" : "swapnil", "class" : 10, "region" : "urban", "school" : "school", "total_socialscience" : 152, "total_science" : 148,
        "total_language" : 202, "avg_science" : 74, "avg_socialscience" : 76, "avg_language" : 67.33333333333333 }
        { "_id" : ObjectId("5aa4bbe212899b0fde851295"), "name" : "rock", "class" : 10, "region" : "rural", "school" : "school", "total_socialscience" : 142, "total_science" : 137,
        "total_language" : 215, "avg_science" : 68.5, "avg_socialscience" : 71, "avg_language" : 71.66666666666667 }
        { "_id" : ObjectId("5aa4bbe212899b0fde851296"), "name" : "mary", "class" : 10, "region" : "urban", "school" : "school", "total_socialscience" : 162, "total_science" : 162,
        "total_language" : 210, "avg_science" : 81, "avg_socialscience" : 81, "avg_language" : 70 }

Student Appeared for exam
    db.studentinfo.aggregate([
                 { $match: { class: 10 } },
                     { $group: { _id: {"region": "$region","class": "$class"}, total: { $sum: 1 } } },
                   ])
    output:
        { "_id" : { "region" : "urban", "class" : 10 }, "total" : 3 }
        { "_id" : { "region" : "rural", "class" : 10 }, "total" : 3 }

Does student 80 or higher mark in marathi 

    db.studentinfo.aggregate(
        { $project: {
        _id: 0,
        name: 1,
        marathiFilterd: {$cond: [
                    {$gt: ['$marathi', 80]},
                    1,
                    0
                ]}
        }},
        { $group: {
        _id: "$name",
         marathiFi: {$sum: '$marathiFilterd'}
        }});

    output:
        { "_id" : "sagar", "marathiFi" : 0 }
        { "_id" : "sangram", "marathiFi" : 1 }
        { "_id" : "sachin", "marathiFi" : 0 }
        { "_id" : "mary", "marathiFi" : 0 }
        { "_id" : "rock", "marathiFi" : 1 }
        { "_id" : "swapnil", "marathiFi" : 0 }


Student with First class in marathi & english:

    db.studentinfo.aggregate(
        { $project: {
        _id: 0,
        class: 1,
        region:1,
        marathiFilterd: {$cond: [
                    {$gt: ['$marathi', 60]},
                    1,
                    0
                ]},
           englishFilterd: {$cond: [
                    {$gt: ['$english', 60]},
                    1,
                    0
                ]}
        }},
        { $group: {
        _id: {class:"$class",region:"$region"},
         marathiFi: {$sum: '$marathiFilterd'},
         englishFi: {$sum: '$englishFilterd'},
         total:{$sum:1}
        }});

output:
    { "_id" : { "class" : 10, "region" : "urban" }, "marathiFi" : 2, "englishFi" : 2, "total" : 3 }
    { "_id" : { "class" : 10, "region" : "rural" }, "marathiFi" : 3, "englishFi" : 3, "total" : 3 }

Project multiple times:

        db.studentinfo.aggregate(
            { $project: {
            _id: 0,
            class: 1,
            region:1,
            marathiFilterd: {$cond: [
                        {$gt: ['$marathi', 60]},
                        1,
                        0
                    ]},
               englishFilterd: {$cond: [
                        {$gt: ['$english', 60]},
                        1,
                        0
                    ]}
            }},
            { $group: {
            _id: {class:"$class",region:"$region"},
             marathiFi: {$sum: '$marathiFilterd'},
             englishFi: {$sum: '$englishFilterd'},
             total:{$sum:1}
            }},
                { $project:  {class : '$_id.class',region:'$_id.region',total:'$total',marathiFirstclass:'$marathiFi',englishFirstclass:'$englishFi',_id:0}}
        );


No comments:

Post a Comment