Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

# MongoDB Puzzler #1

DZone 's Guide to

# MongoDB Puzzler #1

· Database Zone ·
Free Resource

Comment (0)

Save
{{ articles[0].views | formatCount}} Views

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:

Topics:

Comment (0)

Save
{{ articles[0].views | formatCount}} Views

Opinions expressed by DZone contributors are their own.

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

{{ parent.tldr }}

{{ parent.urlSource.name }}