Understanding $not in combination with $elemMatch in MongoDB

Question

I have this document structure:

{
      "name": "ab",
      "grades": [
        {
          "grade": "A",
          "score": 1
        },
        {
          "grade": "A",
          "score": 12
        },
        {
          "grade": "A",
          "score": 7
        }
      ],
      "borough": "Manhattan2"
    }

Assignment is to write a query to find the restaurants that have all grades with a score greater than 5.

The solution to the problem is following:

db.restaurants.find({
      "grades": {
        "$not": {
          "$elemMatch": {
            "score": {
              "$lte": 5
            }
          }
        }
      }
    })

I have troubles understanding proposed solution.

So far I have only used $elemMatch to match at least one element of array or elements in array's inner objects (grades.score), but how the heck $not is "somehow making" $elemMatch to check for all grades.score in this object?

I do understand general idea, "don't look at score less the equal to 5, and what remains is what we need", but I cannot comprehend what does this code snippet returns:

"$not": {
      "$elemMatch": {
        "score": {
          "$lte": 5
        }
      }
    }

If was asked what does this query do before running & testing it, I would say that it find first score that is greater then 5 and takes that document, but that is wrong, and I cannot figure why. I see that order of fields and keywords plays some role but don't see the connection.

Answer 1

From the docs for $elemMatch

The $elemMatch operator matches documents that contain an array field with at least one element that matches all the specified query criteria.

{ "$elemMatch": { "score": { "$lte": 5 } } } will match documents which have at least one array element with score <= 5.

• So $not on that will match documents which don't meet that criteria
• ie "none of the elements have score <= 5"
• And that's the same as "all of the elements have score > 5"

Answer 2

In order to understand $elemMatch and $not, it would help if we start with $all.

As you we all know, $not is equal to say “No”, the opposite to “Yes”. We shall come to “No” later. let us first start discussing $all, $elemMatch under the context of “Yes”.

Let us have a sample collection of just one document as below.

In Mongo shell:

let t = db.test;
t.find();
[ a: [ 1, 2 ] } ]

First of $all :

Query 1 : found

t.find({a: { $all:[1] }});
[ { a: [ 1, 2 ] } ]

Query 2: found

t.find({a: { $all:[1,2] }});
[ {  a: [ 1, 2 ] } ]

Query 3: Not found

t.find({a: { $all:[1,2,3] }});
<No document>

What does it mean by $all ?

Th above three queries can be described in one line as “find all documents with the array key “a” has all elements in the given array”.

• Query 1 : a has all elements in [1] - found
• Query 2 : a has all elements in [1,2] - found
• Query 2 : a does not have all elements in [1,2,3] - not found

Now let us see $elemeMatch

Query 4 : found

t.find({a : { $elemMatch: {$eq: 1}}});
[ { a: [ 1, 2 ] } ]

Query 5 : found

t.find({a : { $elemMatch: {$eq: 2}}});
[ { a: [ 1, 2 ] } ]

Query 6 : Not found

t.find({a : { $elemMatch: {$eq: 3}}});
<No document>

What does it mean by $elemMatch ?

The above three queries can be described in one line as “find all documents with the array key “a” has at least one element matching the given condition”.

• Query 4 : a has one element matching the condition equal to 1 - found
• Query 5 : a has one element matching the condition equal to 2 - found
• Query 6 : a has no element matching the condition equal to 3 - not found

The take-away:

1. Although the two set of queries with $all and $elemMatch yielded the same results, the interpretation of the queries are different - as it is described above.
2. Now all these queries are in the context of “Yes”.
3. If we are able to understanding the basics of these six queries, we can easily guess what will be the outcome of the same 6 queries in “No” context. It would be just the opposite results.

Please see the test results.

t.find({a: {$not: { $all:[1] }}}); // no data
t.find({a: {$not: { $all:[2] }}}); // no data
t.find({a: {$not: { $all:[3] }}}); // data found
        1. [ { a: [ 1, 2 ] } ]
t.find({a : {$not : { $elemMatch: {$eq: 1}}}}); // no data
t.find({a : {$not : { $elemMatch: {$eq: 2}}}}); // no data
t.find({a : {$not : { $elemMatch: {$eq: 3}}}}); / data found
        1. [ { a: [ 1, 2 ] } ]

Notes:

$elemMatch can take more than one conditionals, for brevity, the examples in this post include just one condition. When there will be more than one condition, it would be evaluated with AND logical operation which is essentially the same as with just one condition.

Thanks

WeDoTheBest4You

Answer 3

Try not to overthink it. The MongoDB query $not operator performs a logical NOT in programming. So whatever results in a truthy value, just invert the result. It's that simple really.

However, that simplistically comes with side effects.

Without the $not your query brings back documents where:

• grades has a score element <=5.

With the $not your query just inverts that and returns:

• all of the other documents.

The side effect is that now you know those returned documents won't have a score element <=5.

The danger is - that is not the same as all grades.score are > 5. This is because included in those results will be documents that:

• don't have a grades array at all.
• grades array is empty []

You can see that play out HERE.

In document databases such as MongoDB a field can exist and not exist in the same collection of documents at the same time because the flexible schemas allow for this and is very common. It's also common for an array to be empty either at initial document creation or through it's lifetime via CRUD operations.

Based on the limited information you have shared the proposed solution in my view is incorrect and you should consider feedback to your tutor with this example.

The correct solution would be:

db.restaurants.find({
  $and: [
    {
      "grades.score": {
        $gt: 5
      }
    },
    {
      "grades.score": {
        $not: {
          $lte: 5
        }
      }
    }
  ]
})

This finds only the documents where grades.score is >5 $and where grades.score is $not <=5. Both conditions need to be met.

You can see an example of that HERE.