Point Networks Point Networks - 8 months ago 25
Node.js Question

How to write query for condition of some objects must NOT be in array of objects and one of some other objects Must be in array of object?

I have student collection of mongo documents like the following:

{
name: 'XYZ',
age: 26,
education: [
{ title: 'abc', university: 'pqr', grade: 'A' },
{ title: 'def', university: 'uvq', grade: 'B' },
{ title: 'ghi', university: 'xyz', grade: 'A' },
{ title: 'jkl', university: 'pqr', grade: 'B' },
{ title: 'mno', university: 'uvw', grade: 'C' }
]
}, {
name: 'QQQ',
age: 26,
education: [
{ title: 'abc', university: 'pqr', grade: 'A' },
{ title: 'ghi', university: 'xyz', grade: 'A' },
{ title: 'jkl', university: 'xyz', grade: 'B' },
{ title: 'mno', university: 'pqr', grade: 'C' }
]
}


Now I want to write a query in which I want students who MUST HAVE completed their


{education-title:'abc' with grade A}
OR
{education-title:'def'
with grade B}



BUT MUST NOT have completed


{education-title:'jkl' with university:pqr}

AND
{education-title:'mno' with university:uvw}



If observed carefully my document with the
name: QQQ
satisfies all the criteria and should be the output of the query. I'm trying to solve these conditions by using
$or
and
$and
operators inside
$elemMatch
operator, but not sure whether my approach is correct or not. My query looks like the following

studentModel.aggregate({
{
$match: {
'education': $elemMatch: {
$or: [{
'title': 'abc',
'grade': 'A'
},
{
'title': 'def',
'grade': 'B'
}
]},
$not: {
$elemMatch: {
$and: [{
'title': 'jkl',
'university': 'pqr'
},
{
'title': 'mno',
'university': 'uvw'
}
]
}
}
}
});


above code is working and giving me the output but I'm not sure if it will work with millions of record and still produce the expected output or not. I just wanna make sure if my approach of using $and AND $or operator inside $elemMatch is correct or not?

Answer Source

When I run your query it incorrectly selects the first document, and that is because the second condition inside the $not actually can never match an element since it is not possible for $elemMatch to contain "multiple conditions" for the same properties on the same element. Which is what $elemMatch is making the distinction of in "matching multiple conditions on the same array element". Hence the naming.

The correct way, is instead to list "separate" $elemMatch statements and wrap them with $all:

db.getCollection('students').find({
  "education": {
    "$elemMatch": {
      "$or": [
        { "title": "abc", "grade": "A" },
        { "title": "def", "grade": "B" }
      ]  
    }, 
    "$not": {
      "$all": [
        { "$elemMatch": {
          "title": "jkl", "university": "pqr"
        }},
        { "$elemMatch": {
          "title": "mno", "university": "uvw"  
        }} 
      ]
    }
  }
})

This correctly only selects the second QQQ document from the provided samples.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download