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.


No comments:

Post a Comment

Please do not enter any spam link in the comment box.

Related Posts

What is the Use of isNaN Function in JavaScript? A Comprehensive Explanation for Effective Input Validation

In the world of JavaScript, input validation is a critical aspect of ensuring that user-provided data is processed correctly. One indispensa...