MongoDB aggregation pipeline

Question

I have this type of documents:

collection:People
{name:"George", grade:5, school:"MathHighSchool"}

and many more examples. I need a query that finds all people who: study in the MathHighSchool (so we have db.people.aggregate({$match:{school:"MathHighSchool"}},....)

and then group them by their grades, as it shows the number of people with grade <3 number of people with grade between 3 and 5 and number of people with grade > 5. Any ideas?

{$group:{_id:"$grade", low:{$lt:3}, medium:{$and:[{lte:5},{gte:3}], high:{gt:5}}}}) something like this but doesn;t work — user3108836
– user3108836, Commented Dec 16, 2013 at 21:12
your question basically corresponds to this one (stackoverflow.com/questions/19967701/…), which is also still unanswered... My first guess would be you need three different queries and put together the results afterwards. — chk
– chk, Commented Dec 16, 2013 at 21:17
maybe this (nrg-media.de/2013/10/…) post could also help, haven't read it in depth — chk
– chk, Commented Dec 16, 2013 at 21:20

Stennie · Accepted Answer · 2013-12-17 01:16:17Z

In order to conditionally sum matches in your $group pipeline step, you need to use the $cond operator.

Test data set up:

db.people.insert([
    {name:"George", grade:5, school:"MathHighSchool"},
    {name:"John", grade:4, school:"MathHighSchool"},
    {name:"Paul", grade:3, school:"MathHighSchool"},
    {name:"Ringo", grade:5, school:"MathHighSchool"},
    {name:"Johnny", grade:2, school:"MathHighSchool"},
    {name:"Joshua", grade:7, school:"MathHighSchool"},
])

Assuming you just want the counts, here is an example aggregation (tested with MongoDB 2.4.8):

db.people.aggregate(
    { $match: {
        school : 'MathHighSchool'
    }},
    { $group: {
        _id: "$school",

        // Add up matches less than grade 3
        low: { $sum: { $cond: [ {$lt: ["$grade", 3] }, 1, 0] }},

        // Add up matches between 3 and 5 (inclusive)
        medium: { $sum: { $cond:[
            { $and: [ {$gte: ["$grade", 3]}, {$lte: ["$grade", 5]} ] }, 1, 0]
        }},

        // Add up matches greater than grade 5
        high: { $sum: { $cond: [ {$gt: ["$grade", 5] }, 1, 0] }},

    }}
)

Result:

{
    "result" : [
        {
            "_id" : "MathHighSchool",
            "low" : 1,
            "medium" : 4,
            "high" : 1
        }
    ],
    "ok" : 1
}

Tyler2P · Accepted Answer · 2021-02-14 11:22:00Z

We can also achieve this solution using switch case in mongo and if condition also, here is a little approach to achieve the solution.

Query:

db.people.aggregate([
    {$match:{"school":"MathHighSchool"}},
    {$project:{"school":1,"div": { $switch:
    {
        branches: [
        {
            case: { $lte : ["$grade", 3 ] },
            then: "less than or equal to 3"
        },
        {
            case: { $and : [ { $gt : ["$grade", 3 ] },
            { $lt : [ "$grade", 5 ] } ] },
            then: "between 3 and 5"
        },
        {
            case: { $gte : [ "$grade", 5] },
            then: "greater than or equal to 5"
        }],
    }}}},
    {$group:{"_id":"$div","count":{$sum:1}}}
]);

Result:

{ 
    "_id" : "between 3 and 5", 
    "count" : 1.0
}
{ 
    "_id" : "less than or equal to 3", 
    "count" : 2.0
}
{ 
    "_id" : "greater than or equal to 5", 
    "count" : 3.0
}

Please Visit here for more detail https://beingcodeexpert.blogspot.com/2021/02/switch-conditional-operator-in-mongodb.html

Collectives™ on Stack Overflow

MongoDB aggregation pipeline

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related