January 25, 2017

How to compare two fields value while querying in mongodb

To compare 2 fields of a document while querying in mongodb, we need to use $where.

Lets see the example to compare 2 fields of a document:

 Employee Collection  
           {    
             "_id" : 1,    
             "name" : {    
                "first" : "Abhi",    
                "last" : "Dev"    
             },    
             "department" : "finance",    
             "joineddate" : "2010-04-10"     
            },    
            {    
             "_id" : 2,    
             "name" : {    
                "first" : "Agrawal",    
                "last" : "Agrawal"    
             },     
             "joineddate" : "2006-07-07"     
            },    
            {    
             "_id" : 3,    
             "name" : {    
                "first" : "Dhruv",    
                "last" : "Agrawal"    
             },    
             "department" : "finance",    
             "joineddate" : "2010-09-07"     
            }  
           }   

Now lets say I need to fetch data which has first and last values are equal.

The query would be:
 db.employee.find({ $where : "this.name.last == this.name.first" })  

The output would be:
{    
             "_id" : 2,    
             "name" : {    
                "first" : "Agrawal",    
                "last" : "Agrawal"    
             }

3 comments:

  1. Thanks, very useful, but how do you compare fields with regex?
    Something like:
    db.employee.find({ $where : "this.name.last like this.name.first" })
    I want that, the query return the following document as a result

    {
    "_id" : 4,
    "name" : {
    "first" : "Agrawal",
    "last" : "BlahAgrawalBlah"
    },
    }

    ReplyDelete
  2. what if they are two date fields

    ReplyDelete
    Replies
    1. it should work the same way, we compared for other fields. in your case it should be:
      db.employee.find({ $where : "this.name.date1 == this.name.date2" })

      Hope that helps.

      Delete