NoSQL Zone is brought to you in partnership with:

Kristina Chodorow is a core contributor to MongoDB. She has written several O'Reilly books (MongoDB: The Definitive Guide, Scaling MongoDB, and 50 Tips and Tricks for MongoDB Developers) and has given talks at conferences around the world, including OSCON, FOSDEM, Latinoware, TEK·X, and YAPC. Her Twitter handle is @kchodorow. Kristina is a DZone MVB and is not an employee of DZone and has posted 52 posts at DZone. You can read more from them at their website. View Full User Profile

MongoDB Puzzler #1

12.29.2012
| 1683 views |
  • submit to reddit

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:

Published at DZone with permission of Kristina Chodorow, author and DZone MVB.

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)