Jul 14, 2016

How to compare two values in MongoDB

Problem: Suppose we have to compare two values and want to return as true or false.

Solution: We can use $cmp operator for this. This is the comparison aggregation operator in mongodb.
  • $cmp returns 0 if first value is equivalent to second value.
  • $cmp returns 1 if first value is greater than second value.
  • $cmp returns -1 if first value is less than second value.


Note $cmp compare both value and type using the specified BSON comparison order for values of different types.

Syntax:


{ $cmp: [ <expression1>, <expression2> ] }


Example: Suppose we have a collection “Order” as following and want to compare the price with value 500.


{ "_id" : 1, "item" : "item1", qty: 1, Price:500}
{ "_id" : 2, "item" : "item1", qty: 2, Price:200 }
{ "_id" : 3, "item" : "item1", qty: 4, Price:300 }
{ "_id" : 4, "item" : "item1", qty: 8, Price:700 }
{ "_id" : 5, "item" : "item1", qty: 2, Price:500 }


Run the following query


db.Order.aggregate(
   [
     {
       $project:
          {
            item: 1,
            Price: 1,
            CmpResult: { $cmp: [ "$Price", 500 ] },
            _id: 0
          }
     }
   ]
)


Result:


{ "item" : "item1", "Price" : 500, "CmpResult" : 0 }
{ "item" : "item1", "Price" : 200, "CmpResult" : -1 }
{ "item" : "item1", "Price" : 300, "CmpResult" : -1 }
{ "item" : "item1", "Price" : 700, "CmpResult" : 1 }
{ "item" : "item1", "Price" : 500, "CmpResult" : 0 }


Real scenario example
Suppose we have a collection “Inventory” which has two fields first one is the “BasePrice” and second one is the “Price”. Here we want to fetch those records which Price is greater than BasePrice.

Note: BasePrice is cost price where Price is the selling price where Shipping, tax etc included.

See following Collection


{ "_id" : 1, "item" : "item1", qty: 1, BasePrice:300, Price:500}
{ "_id" : 2, "item" : "item1", qty: 2, BasePrice:250, Price:200 }
{ "_id" : 3, "item" : "item1", qty: 4, BasePrice:250, Price:300 }
{ "_id" : 4, "item" : "item1", qty: 8, BasePrice:600, Price:700 }
{ "_id" : 5, "item" : "item1", qty: 2, BasePrice:502, Price:500 }


Run the following query


db.Inventory.aggregate(
   [
     {
       $project:
          {
            item: 1,
            Price: 1,
            BasePrice:1,
            CmpResult: { $cmp: [ "$Price", "$BasePrice" ] },
            _id: 0
          }
     },
     {$match: {CmpResult: {$gt: 0}}}
   ]
)


Result:


{ "item" : "item1", "BasePrice" : 300, "Price" : 500, "CmpResult" : 1 }
{ "item" : "item1", "BasePrice" : 250, "Price" : 300, "CmpResult" : 1 }
{ "item" : "item1", "BasePrice" : 600, "Price" : 700, "CmpResult" : 1 }


In above query we fetch those records which Price is greater than BasePrce like this we can fetch those records with BasePrce is greater than Price.

Note:  Here we can use $where operator to fetching records but using $where is a bad idea, as it force the use of the javascript comparison engine which is probably the slowest way to do this.

Thanks Guys.