Over a million developers have joined DZone.

MongoDB Puzzler #1

· Database Zone

Build fast, scale big with MongoDB Atlas, a hosted service for the leading NoSQL database. Try it now! Brought to you in partnership with MongoDB.

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:

Now it's easier than ever to get started with MongoDB, the database that allows startups and enterprises alike to rapidly build planet-scale apps. Introducing MongoDB Atlas, the official hosted service for the database on AWS. Try it now! Brought to you in partnership with MongoDB.

Topics:

The best of DZone straight to your inbox.

SEE AN EXAMPLE
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.
Subscribe

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

{{ parent.tldr }}

{{ parent.urlSource.name }}