MongoDB Puzzler #1

Suppose that the collection test.foo contained the following documents:

{"x": -5}
{"x": 0}
{"x": 5}
{"x": 10}
{"x": [0, 5]}
{"x": [-5, 10]}
{"x": [-5, 5, 10]}

x is some combination of -5, 0, 5, and 10 in each document. Which documents would db.foo.find({"x" : {"$gt" : -1, "$lt" : 6}}) return?

Scroll Down to See Ansser

Answer

You’d get:

{"x" : 0}
{"x" : 5}
{"x" : [0, 5]}
{"x" : [-5, 10]} // what the what?{"x" : [-5, 5, 10]}

If you are familiar with the way MongoDB queries arrays, you should expect any array containing 0 or 5 to match, which it does. However, you may wonder why you get {"x" : [-5, 10]}, where neither element is between -1 and 6.

The answer comes from deep in the bowels of MongoDB’s query language: when you query for a range and the document contains an array, each query clause must match at least one array element. However, all of the clauses don’t have to match the same array element: each clause can match a different element! So -5 matches {"$lt" : 6} and 10 matches {"$gt" : -1}!

There are several ways to make this query do what you’d want/expect:

In this particular example, we could use an $in instead of a range:

> db.foo.find({x: {$in : [0, 5]}})
{"x" : 0}
{"x" : 5}
{"x" : [0, 5]}
{"x" : [-5, 5, 10]}

If you really do want a range, you could use $elemMatch to specify that you want both of the clauses to match a single element:

> db.foo.find({x: {$elemMatch: {$gt : -1, $lt : 6}}})
{"x" : [0, 5]}
{"x" : [-5, 5, 10]}

However, $elemMatch does not work on scalars, so that query will no longer return the {"x" : 0} and {"x" : 5} matches. If you have an index on x, you can use min() and max() to specify that you only want MongoDB to traverse the index between -1 and 6, e.g.,

> db.foo.find({"x" : {"$gt" : -1, "$lt" : 6}}).min({"x" : -1}).max({"x" : 6})
{"x" : 0}
{"x" : [ 0, 5 ]}
{"x" : 5}
{"x" : [ -5, 5, 10 ]}

This will prevent MongoDB from trying to find matches outside of the -1-6 range.

Thus, if you have just scalars, you don’t have to worry about this. If you have just arrays, use $elemMatch. If you are storing a mix, figure out what behavior you want and implement accordingly.

(For what it’s worth, I think this behavior should be changed, but it was actually a design decision.)

If you liked this, you might enjoy: