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?
Click here to show answer
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.)
jQuery(‘#puzzlers-0-toggle’).toggle(
function() {
jQuery(this).text(“Click here to hide answer”);
jQuery(‘#puzzlers-0-answer’).css(‘display’, ‘block’);
},
function() {
jQuery(this).text(“Click here to show answer”);
jQuery(‘#puzzlers-0-answer’).css(‘display’, ‘none’);
}
);
Kristina Chodorow's Blog 
Why is {“x” : [ -5, 5, 10 ]} appearing in all solutions?
LikeLike
5 and 10 are greater than -1. -5 is less than 6.
LikeLike
Hello everyone!
I need help please . I’m trying use MongoDB in a app, but I have some problem.
For example a have a collection whit the following data:
{
activitty :”title of main Activity”
activittytype :”SPORT”
description: “description of the activity”
activityinfo:[
{ subactivity: “title of the sub activity-1”,
subactivittypey: “FOOTBALL”,
description :”description of the sub activity-1″
startdate: “Date-1″
address:{ Country:”countryname-1″,
city:”cityname-1”,
location:{ lon: -9.87,
lat: 43.98 }
},
{
subactivity: “title of the sub activity-2”, subactivittypey: “TENNIS”, description :”description of the sub activity-2″
startdate: “Date-2″
address:{ Country:”countryname-2″,
city:”cityname-2”,
location:{ lon: -7.5587,
lat: 38.4498 }
},
{ subactivity: “title of the sub activity-3”, subactivittypey: “BASKETBALL”, description :”description of the sub activity-3″ startdate: “Date-3″ address:{ Country:”countryname-3″, city:”cityname-2”, location:{ lon: 23.5587, lat: 68.4498 } }
]
}
If I want search for all documents that have some subactivity of type TENNIS, how can I do it using $near to find them if there are more that one “location” in the document ?
Thanks…
LikeLike
> db.test.find({“x” : {“$gt” : -1, “$lt” : 6}}).min({“x” : -1}).max({“x” : 6})
error: {
“$err” : “no index found for specified keyPattern: {} min: { x: -1.0 } m
ax: { x: 6.0 }”,
“code” : 10367
}
why ??
LikeLike
A mentioned above and in the error message, you need an index on x.
LikeLike