How to compare two fields in MongoDB

In this MongoDB tutorial, we are going to discuss how to compare two fields in MongoDB. We will cover this topic with different operations and examples. These are the following topics that we are going to cover in this tutorial:

• MongoDB compare two fields query
• MongoDB compare two fields equal
• MongoDB compare two fields not equal
• MongoDB compare two fields in the same document
• MongoDB compare two fields using python

MongoDB compare two fields query

In MongoDB, the $where query operator is used for comparing the fields or documents that satisfy a JavaScript expression.

We can also pass a string containing a JavaScript expression or a JavaScript function by using this operator.

The $where provides higher flexibility, but requires that the database processes the JavaScript expression or function for each document in the collection. The documents in the JavaScript expression or function using either this or obj.

Syntax:

{ $where: <string|JavaScript Code> }

Note that, we can only apply the $where query operator top-level documents. The $where query operator will not work inside the nested documents.

Read Missing before statement MongoDB

MongoDB compare two fields equal

In this topic, you will learn to use the $where query operator to compare two fields equal (string) with the help of an example. So, let us understand with the help of an example.

Example:

The following documents will insert into the info collection.

db.info.insertMany([
{
   "_id": 1,
   "FullName": {
      "FirstName": "John",
      "LastName": "Smith"
   },
   "BranchName": "ComputerScience"
},
{
   "_id": 2,
   "FullName": {
      "FirstName": "Smith",
      "LastName": "Smith"
   },
   "BranchName": "Civil"
},
{
   "_id": 3,
   "FullName": {
      "FirstName": "David",
      "LastName": "Smith"
   },
   "BranchName": "Mechanical"
}
])

We have successfully inserted the documents into the collection. We will apply the below query to compare the two fields.

db.info.find(
    { $where: "this.FullName.FirstName == this.FullName.LastName" }
).pretty()

Here, we have compared two string fields FirstName and LastName using $where operator and the find() method will retrieve all those documents that satisfy the condition.

We have successfully compared two fields and returned documents where the two fields same.

Read Sub-process /usr/bin/dpkg returned an error code (1)

MongoDB compare two fields not equal

In this topic, we will compare two fields of the documents and find documents that are not equal. You will more understand with the help of an example so let’s get into it.

Example:

Here, we will use the same collection info that we have used in the previous topic. For reference:

Now, we will apply the below query to compare two fields of the documents and find those are not equal.

db.info.find(     
   { $where: "this.FullName.FirstName != this.FullName.LastName"} ).pretty()

Here, we have used the $where query operator to compare two fields FirstName and LastName and use the not equal ” != “ operator to retrieve those documents that are not equal.

We have successfully retrieved those documents where compared string is not equal.

Read MongoDB two-phase commit

MongoDB compare two fields in the same document

In this topic, you will learn to compare two fields in the same document. You can use the $where operator to compare the fields.

When we have a given condition where we have to compare multiple properties on the same field. Let us understand with the help of an example.

Example:

The following documents were inserted into the Test collection.

db.Test.insertMany([
{ "_id": 101, "Name": "Peter", "Scores": [ 56, 78 ], "City": "USA" },
{ "_id": 102, "Name": "Jack", "Scores": [ 88, 45 ], "City": "Canada" },
{ "_id": 103, "Name": "Jonas", "Scores": [ 98, 79 ], "City": "UK" }
])

Now, we will apply the below query to compare two fields in the same document.

db.Test.find({ $where : "this.Scores[0] > this.Scores[1]" })

Here, we have applied the $where query operator and compare the Scores field indexes values. The find() method will find those documents where the first value is greater than the second.

We have successfully retrieved the documents where the score of one is greater than the second.

Read MongoDB shutting down with code 48

MongoDB compare two fields using python

In this topic, you will learn to compare two fields in MongoDB using Python. we can use the $where query operator to find the documents after comparing the two fields. Let us understand with the help of an example.

Example:

In the following code, we have imported the pymongo library to use MongoDB in python and After that use the MongoClient class to connect with the MongoDB server.

Here, we have also created a new database demo and inserted some documents into the Info collection. And, we have used the find() function to retrieve the documents and in the parameter, the $where query operator is used to compare two fields.

import pymongo

# creating a MongoClient object and connect with the host
myclient = pymongo.MongoClient("mongodb://127.0.0.1:27017")

# accessing the database
mydb = myclient["demo"]

# accessing the collection of the database
mycol = mydb["Info"]
docs = [{ "_id": 101, "Name": "Peter", "Scores": [ 56, 78 ] },
        { "_id": 102, "Name": "Jack", "Scores": [ 88, 45 ] },
        { "_id": 103, "Name": "Jonas", "Scores": [ 98, 79 ] }]
mycol.insert_many(docs)

# compare the field of same documents 
result = mycol.find({ "$where" : "this.Scores[0] < this.Scores[1]" })
for compare_fields in result:
    print(compare_fields)

See the execution of the code with output:

We have successfully found the documents after comparing two fields using python.

You may like the following MongoDB tutorials:

• MongoDB create and change user password
• MongoDB find multiple conditions
• How to delete documents in MongoDB
• MongoDB find by ID
• Display MongoDB data in HTML
• MongoDB Update Documents
• Unable to locate package mongodb-org
• MongoDB remove an element from the array

So, In this tutorial, you have learned about “Compare two fields in MongoDB” with different operations and examples. And we have also covered the following list of topics:

• MongoDB compare two fields query
• MongoDB compare two fields equal
• MongoDB compare two fields not equal
• MongoDB compare two fields in the same document
• MongoDB compare two fields using python