Over a million developers have joined DZone.

MongoDB Puzzler #1

DZone's Guide to

MongoDB Puzzler #1

· Database Zone
Free Resource

Check out the IT Market Clock report for recommendations on how to consolidate and replace legacy databases. Brought to you in partnership with MariaDB.

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


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:

Interested in reducing database costs by moving from Oracle Enterprise to open source subscription?  Read the total cost of ownership (TCO) analysis. Brought to you in partnership with MariaDB.


Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}