Jul 14, 2016

Query to fetch records where field is equal to particular value in MongoDB

Problem: Suppose we have to fetch records from a collection which one field is equal to particular value?

Solution: We can use $eq operator to fetch records from collection which one field has particular value. This is the comparison aggregation operator in mongodb.
  • $eq returns true if values are equivalent.
  • $eq returns false if values are not equivalent.

Syntax:


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


Example: Suppose we have a collection of order which like following.


{ "_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,
            EqualResult: { $eq: [ "$Price", 200 ] },
            _id: 0
          }
     }
   ]
)


Result:


{ "item" : "item1", "Price" : 500, "EqualResult" : false }
{ "item" : "item1", "Price" : 200, "EqualResult" : true }
{ "item" : "item1", "Price" : 300, "EqualResult" : false }
{ "item" : "item1", "Price" : 700, "EqualResult" : false }
{ "item" : "item1", "Price" : 500, "EqualResult" : false }


Example: From above collection if we want select those records which price value is equal to 200 and quantity is greater than 1.

Run the following query


db.Order.aggregate(
     {$match: { $and: [{ Price: {$eq:200}}, { qty:{$gt:1}}] }}
)

Or

db.Order.aggregate(
     {$match: { $and: [{ Price:200}, { qty:{$gt:1}}] }}
)
Both are same.


Result:


{ "_id" : 2, "item" : "item1", "qty" : 2, "Price" : 200 }