How to filter sub-document in mongoDB?

Question

I have this mlab document:

{
"_id": {
    "$oid": "572b2cdfc80eb653c302f5e9"
},
"year": 2014,
"students": [
    {
        "id": 5,
        "firstName": "Joe",
        "lastName": "know",
        "GPA": 67
    },
    {
        "id": 3,
        "firstName": "Peter",
        "lastName": "Jones",
        "GPA": 77
    },
    {
        "id": 6,
        "firstName": "Yossi",
        "lastName": "Haim",
        "GPA": 68
    },
    {
        "id": 13,
        "firstName": "Chen",
        "lastName": "Si",
        "GPA": 92
    }
]
}

Which i get with: (year is also a parameter)

gradeM= mongoose.model('Grade',grade);
      gradeM.find({'year':year},'-_id').exec(function (err, data) {
          if (err) console.log("err: " + err);
      console.log(JSON.stringify(data)); 
  });

As much as getting data from the collection i'm doing well but i wish to exclude from returned document all students array records in which GPA<90. Tried some different aggregate ans match functions but i can't seem to get the syntax correct.

End result should look like:

 {
   "year": 2014,
   "students": [
    {
        "id": 13,
        "firstName": "Chen",
        "lastName": "Si",
        "GPA": 92
    }
]
}

I can always loop the document using JS but i'm looking for a way to get the ready result right away.
Thanks

Sumeet Kumar Yadav · Answer 1 · 2016-05-06T10:47:15.527

2

You can try using aggregation

gradeM.aggregate( {$match: {year: 2014}},{ $unwind: '$students'},
                   { $match: {'students.GPA': {$lt: 90}}},
                   { $group: {_id: '$_id',
                              year : '$year',
                              students: {$push: {id:'$students.id',
                                                 firstName:'$students.firstName'}}}})

Output :

{ "_id" : ObjectId("572b2cdfc80eb653c302f5e9"), 
  "year":2014,
   "students" : [ { "id" : 5, "firstName" : "Joe" },
                  { "id" : 3, "firstName" : "Peter" },
                  { "id" : 6, "firstName" : "Yossi" } ] }

Also ,You can give try

gradeM.aggregate([
    { $match: {year: year}},
    { $project: {
        students: {$filter: {
            input: '$students',
            as: 'students',
            cond: {$lt: ['$$students.GPA', 90]}
        }}
        }}
    ])

edited May 06 '16 at 10:47

answered May 06 '16 at 10:00

Sumeet Kumar Yadav

11,912
6
43
80

first one returnes [] which shouldn't be empty according to the data i have. second one falls if i'm not mistaken because of mongoDB version that doesn't recognize $filter. thanks for the help – Yush N May 06 '16 at 10:18
1

Let me try ..just a moment – Sumeet Kumar Yadav May 06 '16 at 10:20
nice approach with $filter - like it! – profesor79 May 06 '16 at 10:39
1

Thanks alot . Happy to help – Sumeet Kumar Yadav May 06 '16 at 18:07

score 2 · Answer 2 · answered May 06 '16 at 10:01

you can use $elemMatch.

Try as following

gradeM= mongoose.model('Grade',grade);
  gradeM.find({'year':year, students: { $elemMatch: GPA: { $lt: 90 } }},'-_id').exec(function (err, data) {
      if (err) console.log("err: " + err);
  console.log(JSON.stringify(data)); 
  });

profesor79 · Accepted Answer · 2016-05-06T10:18:54.087

1

you can $unwind students, filter them, then add to group - please see example below:

db.yush.aggregate([
{$match:{"year":2014}},
{
            $unwind : "$students"
        }, {
            $match : {
                "students.GPA" : {
                    $lt : 90
                }                
            }
        }, {
            $group : {
                _id : "$year"
                ,
                students : {
                    $push : "$students"
                }
            }
        }
    ])

and at the end use $project to change _id to year

Have a fun - any comments welcome!

edited May 06 '16 at 10:18

answered May 06 '16 at 09:59

profesor79

9,213
3
31
52

This looks good , it gives me all GPA>=90 as needed but it returnes it for all years. how can i filter according to the year parameter i'm getting into the function? Thanks a lot – Yush N May 06 '16 at 10:14
1

@YushN added $match phase as a first one to process only selected year – profesor79 May 06 '16 at 10:20
works great with the explixit year but once i write the parameter year instead (which has the same value) i get []. Thanks – Yush N May 06 '16 at 10:33
@YushN - hmm that's strane - have no idea – profesor79 May 06 '16 at 10:43
and the winner is: "year":parseInt(year) @profesor79 next on the list, trying to understand how to use the aggregate callback return as a http response which i can display on the browser – Yush N May 06 '16 at 18:06

How to filter sub-document in mongoDB?

3 Answers3