Specify Multiple Criteria for Array Elements

Question

I am reading the docs for mongodb here, I am not able to understand these two commands and the different between them.

db.users.find( { finished: { $elemMatch: { $gt: 15, $lt: 20 } } } )

My Understanding : At least one element needs to satisfy both the conditions together.

and

Combination of Elements Satisfies the Criteria ...one element can satisfy the greater than 15 condition and another element can satisfy the less than 20 condition, or a single element can satisfy both

db.users.find( { finished: { $gt: 15, $lt: 20 } } )

Question : How range matching on arrays happen? Is it like if one element satisfies $gt:15, this condition is used up and other elements are check for the rest conditions i.e. $lt:20?

Answer 1

To understand what the documentation is saying you first need to understand how range query with array works.

Suppose you have the following document in your collection:

{ "finished" : [ 27, 3 ] },
{ "finished" : 17 }

The first query:

db.users.find( { "finished": { "$elemMatch": { "$gt": 15, "$lt": 20 } } } )

Will only return the document where "finished" is an array. This is because $elemMatch operator only matches documents where the field is an array and where a single element satisfy all the query criteria.

But the second query:

db.users.find( { "finished": { "$gt": 15, "$lt": 20 } } )

will return both documents which is probably not what you want as 27 is greater than 20 and 3 is less than 15. This is because 27 matches the first criteria and 3 the second. This behavior is what is mentioned in the documentation.

...one element can satisfy the greater than 15 condition and another element can satisfy the less than 20 condition, or a single element can satisfy both:

Conclusion:

Range queries against arrays will match as far as one or multiple elements in the array that match all the query criteria.

Lesson:

Don't use range query with arrays. You will get unexpected result.

Answer 2

Suppose we have these two documents in our collection:

{
        "_id" : ObjectId("57548c14da05625a928404fd"),
        "finished" : [16, 21]
},
{
        "_id" : ObjectId("57548c1bda05625a928404fe"),
        "finished" : [3, 36]
}

Suppose you want all documents where the finished array contains at least one element that is greater than 15 and less than 20 (Obviously only 16 in first document matches with this criteria). If you issue:

db.users.find( { finished: { $gt: 15, $lt: 20 } } )

Instead of just matching with the first document, it would match with both of them. Why?

The problem with the preceding query was that the field references aren't restricted to a single finished element. So, this query will match as long as one of the finished element is greater that 15 and the other is less than 20, but what you want is for both attributes to apply to the same finished element. The Combination of Elements Satisfies the Criteria section in MongoDB documentation stated this fact by:

The following example queries for documents where the finished array contains elements that in some combination satisfy the query conditions; e.g., one element can satisfy the greater than 15 condition and another element can satisfy the less than 20 condition, or a single element can satisfy both

In order to restrict the matching process, you should use the $elemMatch operator:

db.users.find( { finished: { $elemMatch: { $gt: 15, $lt: 20 } } } )