MongoDb: Search value in ranges in different situations

Question

First.

We have follows records in db

{ _id:1, values: [ 1 ,5 ,6 ,8]},
{ _id:2, values: [5 ,7 ,8,10 ,40 ,1]},
{ _id:3, values: [50 ,60 ,5 ,1 ]}

I need query all records whitch consist 'values' from range 8 - 10. Result must be {_id:1},{_id:2}

query ({values:{'$gte':8,'$lte':10}}) return all records and it is not correct result, due to 'values' is array!!

Second.

We have follows records in db

{_id:1, from: 1, to:100},
{_id:2, from: 101, to:200},
{_id:3, from: 201, to:300},
{_id:4, from: 301, to:400} ...

I need found records with element 205 within range from-to . Result {_id:3}

query({from:{'$lte':205},to:{'$gte':205})

is very slow and dont use any indexes {from:1,to:1} at all;

I'm a bit confused. Can anybody help, please.

THank you.

Answer 1

Case 1: Range query over array values

EDIT: i did the test using wrong values.

As documentation explains, using conditional operators over array values (AND implicit operator), only needs to match one condition to return the document.

So,

• _id:1 matches $lte and $gte clauses: OK
• _id:2 matches $lte and $gte clauses: OK
• _id:3 matches only $lte (5 < 10 and 1 < 10) clause: NOT OK but works as intended as the documentation explains.

If you need to filter using this range queries over the array values you have to wrap the values using objects, as follows:

db.test_col2.insert({values:[{v:1} ,{v:5 },{v:6} ,{v:8}]})
db.test_col2.insert({values:[{v:5 },{v:7} ,{v:8},{v:10 },{v:40} ,{v:1}]})
db.test_col2.insert({values: [{v:50} ,{v:60} ,{v:5} ,{v:1} ]})
db.test_col2.find({values: {$elemMatch:{v:{$lte:10, $gte:8}}} })

{"_id":ObjectId("51273098140d09d9105739b5"),"values":[{"v":1},{"v":5},{"v":6},{"v":8}]}
{"_id":ObjectId("51273098140d09d9105739b6"),"values":[{"v":5},{"v":7},{"v":8},{"v":10},{"v":40},{"v":1}]}

If you want to use an index for this query, you can do it as follows:

db.test_col2.ensureIndex({"values.v":1})
db.test_col2.find({values: {$elemMatch:{v:{$lte:10, $gte:8}}} }).explain()
{
  "cursor": "BtreeCursor values.v_1",
  "isMultiKey": true,
...
}

Case 2: Hitting the index using open ranges

As you can see this query hits the index as expected.

for(var i=0 ; i<120000 ; i++) {
... db.test_col.insert({from: (Math.random()*100)%100, to: (Math.random()*100)%100});
... }
> db.test_col.ensureIndex({from:1, to:1})
> db.test_col.count()
120002
> db.test_col.find({from:{$gte:3}, to:{$lt:60}}).explain()
{
    "cursor" : "BtreeCursor from_1_to_1",
    "isMultiKey" : false,
    "n" : 69741,
    "nscannedObjects" : 69902,
    "nscanned" : 116563,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 0,
    "nChunkSkips" : 0,
    "millis" : 340,
    "indexBounds" : {
        "from" : [
            [
                3,
                1.7976931348623157e+308
            ]
        ],
        "to" : [
            [
                -1.7976931348623157e+308,
                60
            ]
        ]
    },
    "server" : "new-host-2.home:27017"
}