Mongo Query question $gt,$lt

Question

I have a query below. I want get items between 4 and 6 so only a:1 should match because it has the value 5 in b.

> db.test.find({ b : { $gt :  4  }, b: {$lt : 6}});
{ "_id" : ObjectId("4d54cff54364000000004331"), "a" : 1, "b" : [ 2, 3, 4, 5 ] }
{ "_id" : ObjectId("4d54d0074364000000004332"), "a" : 2, "b" : [ 2, 4, 6, 8 ] }
>

Can someone tell be why a:2 is matching this query? I can't really see why it is being returned.

I also tried what was specified in the tutorial but id did not seem to work:

> db.test.find({ b : { $gt :  4, $lt : 6}});
{ "_id" : ObjectId("4d54cff54364000000004331"), "a" : 1, "b" : [ 2, 3, 4, 5 ] }
{ "_id" : ObjectId("4d54d0074364000000004332"), "a" : 2, "b" : [ 2, 4, 6, 8 ] }
>

And this one to avoid any confusion regarding GT/GTE

> db.test.find({b: {$gt: 4.5, $lt: 5.5}});
{ "_id" : ObjectId("4d54cff54364000000004331"), "a" : 1, "b" : [ 2, 3, 4, 5 ] }
{ "_id" : ObjectId("4d54d0074364000000004332"), "a" : 2, "b" : [ 2, 4, 6, 8 ] }
>

only a:1 should be returned.

As suggested, I gave $elemMatch a try but it did not appear to work either (objectIds are different because I am on a different machine)

> db.test.find();
{ "_id" : ObjectId("4d5a24a5e82e00000000433f"), "a" : 1, "b" : [ 2, 3, 4, 5 ] }
{ "_id" : ObjectId("4d5a24bbe82e000000004340"), "a" : 2, "b" : [ 2, 4, 6, 8 ] }
> db.test.find({b: {$elemMatch: {$gt : 4, $lt: 6 }}});
>

No documents were returned.

Answer 1

This is a really confusing topic. I work at 10gen and I had to spend a while wrapping my head around it ;)

Let's walk through how the query engine processes this query.

Here's the query again:

> db.test.find({ b : { $gt :  4, $lt : 6}});

When it gets to the record that seems like it shouldn't match...

{ "_id" : ObjectId("4d54cff54364000000004331"), "a" : 1, "b" : [ 2, 4, 6, 8 ] }

The match is not performed against each element of the array, but rather against the array as a whole.

The comparison is performed in three steps:

Step 1: Find all documents where b has a value greater than 4

b: [2,4,6,8] matches because 6 & 8 are greater than 4

Step 2: Find all documents where b has a value less than 6

b: [2,4,6,8] matches because 2 & 4 are less than 6

Step 3: Find the set of documents that matched in both step 1 & 2.

The document with b: [2,4,6,8] matched both steps 1 & 2 so it is returned as a match. Note that results are also de-duplicated in this step, so the same document won't be returned twice.

If you want your query to apply to the individual elements of the array, rather than the array as a whole, you can use the $elemMatch operator. For example

> db.temp.find({b: {$elemMatch: {$gt: 4, $lt: 5}}})
> db.temp.find({b: {$elemMatch: {$gte: 4, $lt: 5}}})
  { "_id" : ObjectId("4d558b6f4f0b1e2141b66660"), "b" : [ 2, 3, 4, 5, 6 ] }

Answer 2

$gt

Syntax: {field: {$gt: value} }

eg:

db.inventory.find( { qty: { $gt: 20 } } )

$lt

Syntax: {field: {$lt: value} }

eg:

db.inventory.find( { qty: { $lt: 20 } } )

eg2:

db.inventory.find({ qty : { $gt :  20, $lt : 60}});

Answer 3

.find( {$and:[ {b:{$gt:4}}, {b:{$lt:6}} ]} )

Answer 4

Below is the detailed document for the understanding,

db.test.insertMany([
    {"_id":1, "x":11, "a":1, "b":[1]},
    {"_id":2, "x":15, "a":4, "b":[1,2,3]},
    {"_id":3, "x":19, "a":5, "b":[1,2,3,4,5]},
    {"_id":4, "x":13, "a":6, "b":[6,8,10]},
    {"_id":5, "x":16, "a":13, "b":[11]},
    {"_id":6, "x":18, "a":11, "b":[5]},
    {"_id":7, "x":15, "a":15, "b":[3,5,7]},
    {"_id":8, "x":12, "a":18, "b":[3,7,9]},
    {"_id":9, "x":14, "a":21, "b":[4,6]}
]);

Below queries are included to make idea clear about comparision,
Query-1: db.test.find({b: {$lt: 6}}); //(any element of b) < 6

{ "_id" : 1, "x" : 11, "a" : 1, "b" : [ 1 ] }
{ "_id" : 2, "x" : 15, "a" : 4, "b" : [ 1, 2, 3 ] }
{ "_id" : 3, "x" : 19, "a" : 5, "b" : [ 1, 2, 3, 4, 5 ] }
{ "_id" : 6, "x" : 18, "a" : 11, "b" : [ 5 ] }
{ "_id" : 7, "x" : 15, "a" : 15, "b" : [ 3, 5, 7 ] }
{ "_id" : 8, "x" : 12, "a" : 18, "b" : [ 3, 7, 9 ] }
{ "_id" : 9, "x" : 14, "a" : 21, "b" : [ 4, 6 ] }

`

Query-2: db.test.find({b: {$gt: 4}, b:{$lt : 6}});// it is translated to db.test.find({b:{$lt : 6}}); hence the outcome of Query-1 and Query-2 is the same.

{ "_id" : 1, "x" : 11, "a" : 1, "b" : [ 1 ] }
{ "_id" : 2, "x" : 15, "a" : 4, "b" : [ 1, 2, 3 ] }
{ "_id" : 3, "x" : 19, "a" : 5, "b" : [ 1, 2, 3, 4, 5 ] }
{ "_id" : 6, "x" : 18, "a" : 11, "b" : [ 5 ] }
{ "_id" : 7, "x" : 15, "a" : 15, "b" : [ 3, 5, 7 ] }
{ "_id" : 8, "x" : 12, "a" : 18, "b" : [ 3, 7, 9 ] }
{ "_id" : 9, "x" : 14, "a" : 21, "b" : [ 4, 6 ] }

Query-3: db.test.find({b: {$gt: 4, $lt: 6}});

{ "_id" : 3, "a" : 5, "b" : [ 1, 2, 3, 4, 5 ] }//(element 5) > 4 and (element 5) < 6` => The matching element is same here element 5
{ "_id" : 6, "a" : 11, "b" : [ 5 ] }//(element 5) > 4 and (element 5) < 6 => The matching element is same here element 5
{ "_id" : 7, "a" : 15, "b" : [ 3, 5, 7 ] }//(element 5) > 4 and (element 5) < 6 => The matching element is same here element 5
{ "_id" : 8, "a" : 18, "b" : [ 3, 7, 9 ] }//(element 5) > 7 and (element 3) < 6 => The matching elements are different i.e. here element 5 and element 3
{ "_id" : 9, "a" : 21, "b" : [ 4, 6 ] }//(element 6) > 4 and (element 4) < 6 => The matching elements are different i.e. here element 4 and element 6

Query-4: db.test.find({b: {$elemMatch: {$gt : 4, $lt: 6 }}});

{ "_id" : 3, "a" : 5, "b" : [ 1, 2, 3, 4, 5 ] }//(element 5) > 4 and (element 5) <6
{ "_id" : 6, "a" : 11, "b" : [ 5 ] }//(element 5) > 4 and (element 5) <6
{ "_id" : 7, "a" : 15, "b" : [ 3, 5, 7 ] }//(element 5) > 4 and (element 5) <6

Query-3 and Query-4 are interesting to know about.
Query-3: List document having array b element x>4 and element y<6. The elements x and y may be the same or the different.
Query-4: List document having array b element x>4 and element y<6. The elements x and y must be the same.