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

I know this late but as suggested no need to change schema, answering as this might help someone like me who came across later point of time :

Case 1: Range query over array values

db.getCollection('your collection').find({values :{$elemMatch :{$gte: 8, $lt: 10}}}, {_id:1})

Basically here we're wrapping two conditions on same array field values using $elemMatch and a second filter/condition to .find is a projection which says output should only contain _id.

Appreciate others for their answers/help..

Answer 2

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"
}

Answer 3

In Case 1:

MongoDB shell version: 2.2.0
connecting to: xxx:9900/test
mongos> db.col2.insert({values:[1,2,3]})
mongos> db.col2.insert({values:[0,5,6]})
mongos> db.col2.find({values:{$lte:3, $gte:1}})
{ "_id" : ObjectId("51272120d96837944b3a2097"), "values" : [ 1, 2, 3 ] }
{ "_id" : ObjectId("5127212bd96837944b3a2098"), "values" : [ 0, 5, 6 ] }

For me, its incorrect. 0 5 6 not in range 1-3.