Difference between the working of $lte:10 and $not:{$gt:10} on an array in MongoDB?

Question

We can see that when using "grades.score":{$lte:10} , score with value greater than 10 also appear in the grades array. But when "grades.score":{$not:{$gt:10}} is used they are filtered out. Though the meaning of both appear as same.

Here is the sample of collection i.e. "restaurants":

{
  "address": {
     "building": "1007",
     "coord": [ -73.856077, 40.848447 ],
     "street": "Morris Park Ave",
     "zipcode": "10462"
  },
  "borough": "Bronx",
  "cuisine": "Bakery",
  "grades": [
     { "grade": "A", "score": 2 },
     { "grade": "A", "score": 6 },
     { "grade": "A", "score": 10 },
     { "grade": "A", "score": 9 },
     { "grade": "B", "score": 14 }
  ],
  "name": "Morris Park Bake Shop",
  "restaurant_id": "30075445"
}

Here is 1st query:

db.restaurants.find({"grades.score":{$lte:10}},{"grades.score":1,name:1,restau rant_id:1,borough:1,cuisine:1,_id:0}).pretty()

Result:

{
    "borough" : "Queens",
    "cuisine" : "Ice Cream, Gelato, Yogurt, Ices",
    "grades" : [
            {
                    "score" : 9
            },
            {
                    "score" : 10
            },
            {
                    "score" : 13
            }
    ],
    "name" : "Carvel Ice Cream",
    "restaurant_id" : "40361322"
}
{
    "borough" : "Brooklyn",
    "cuisine" : "Delicatessen",
    "grades" : [
            {
                    "score" : 4
            },
            {
                    "score" : 3
            },
            {
                    "score" : 10
            }
    ],
    "name" : "Nordic Delicacies",
    "restaurant_id" : "40361390"
}
{
    "borough" : "Manhattan",
    "cuisine" : "American ",
    "grades" : [
            {
                    "score" : 12
            },
            {
                    "score" : 16
            },
            {
                    "score" : 9
            },
            {
                    "score" : 13
            },
            {
                    "score" : 11
            }
    ],
    "name" : "Glorious Food",
    "restaurant_id" : "40361521"
}
{
    "borough" : "Brooklyn",
    "cuisine" : "American ",
    "grades" : [
            {
                    "score" : 11
            },
            {
                    "score" : 2
            },
            {
                    "score" : 13
            },
            {
                    "score" : 11
            }
    ],
    "name" : "The Movable Feast",
    "restaurant_id" : "40361606"
}
{
    "borough" : "Queens",
    "cuisine" : "Delicatessen",
    "grades" : [
            {
                    "score" : 12
            },
            {
                    "score" : 9
            },
            {
                    "score" : 7
            },
            {
                    "score" : 10
            }
    ],
    "name" : "Sal'S Deli",
    "restaurant_id" : "40361618"
}

Here is 2nd query:

db.restaurants.find({"grades.score":{$not:{$gt:10}}}, {"grades.score":1,name:1,restaurant_id:1,borough:1,cuisine:1,_id:0}).pretty()

Result:

{
    "borough" : "Bronx",
    "cuisine" : "American ",
    "grades" : [
            {
                    "score" : 5
            },
            {
                    "score" : 3
            },
            {
                    "score" : 4
            },
            {
                    "score" : 9
            }
    ],
    "name" : "African Market (Baboon Cafe)",
    "restaurant_id" : "40368026"
}
{
    "borough" : "Staten Island",
    "cuisine" : "Italian",
    "grades" : [
            {
                    "score" : 10
            },
            {
                    "score" : 3
            },
            {
                    "score" : 6
            },
            {
                    "score" : 10
            }
    ],
    "name" : "Roadhouse Restaurant",
    "restaurant_id" : "40368034"
}
{
    "borough" : "Manhattan",
    "cuisine" : "French",
    "grades" : [
            {
                    "score" : 9
            },
            {
                    "score" : 2
            },
            {
                    "score" : 8
            }
    ],
    "name" : "Pergola Des Artistes",
    "restaurant_id" : "40369139"
}
{
    "borough" : "Brooklyn",
    "cuisine" : "Hamburgers",
    "grades" : [
            {
                    "score" : 10
            },
            {
                    "score" : 7
            },
            {
                    "score" : 5
            },
            {
                    "score" : 10
            }
    ],
    "name" : "Mcdonald'S",
    "restaurant_id" : "40369535"
}
{
    "borough" : "Brooklyn",
    "cuisine" : "American ",
    "grades" : [
            {
                    "score" : 10
            },
            {
                    "score" : 8
            }
    ],
    "name" : "The River Cafe",
    "restaurant_id" : "40369608"
}

score 0 · Answer 1 · edited Jun 20 '20 at 09:12

Neither of your queries actually filter items out of the grades array.

Query a Field that Contains an Array

If a field contains an array and your query has multiple conditional operators, the field as a whole will match if either a single array element meets the conditions or a combination of array elements meet the conditions.

Your 1st query,

db.restaurants.find({"grades.score":{$lte:10}},{"grades.score":1,name:1,restaurant_id:1,borough:1, cuisine:1,_id:0}).pretty()

returns documents that contain at least one item in the grades array that has a score value less than or equal to 10.

Your 2nd query,

db.restaurants.find({"grades.score":{$not:{$gt:10}}},{"grades.score":1,name:1,restaurant_id:1,borough:1,cuisine:1,_id:0}).pretty()

returns documents that don't have any item in the grades array that has a score value greater than 10.

What I assume you intended to do, is described in this SO answer.

Thans to answer but what i assume that both query should give exactly equal result. So how are they giving different results ? — shashi, Jul 28 '16 at 19:04
If all documents had a single item in the `grades` array, then you would get the same results. `.find({"grades.score": ... })` returns all items of the array if at least a single element in that array matches the condition. — κροκς, Jul 28 '16 at 19:16

Difference between the working of $lte:10 and $not:{$gt:10} on an array in MongoDB?

1 Answers1