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.