MongoDB $expr Expressive Query Operator

The $expr operator in MongoDB is an Expressive Query Operator. It does more than the single operator like $and and $or. It allows the use of aggregation expressions within the query language.

The syntax of $expr operator is given below:

{ $expr: { <expression> }}

It also allows us to use variables and conditional statements. Let’s see an example of this.

In our previous example of comparing operators like $eq and $ne, we were comparing field values to the given values. But we can’t compare fields within the same document to each other with these operators. With the $expr operator, we can easily do that.

Let’s see the example from the given document below:

We have an employee collection and have the following documents:

{
        "_id" : ObjectId("62f77a41c41dfbf8c1e24e17"),
        "name" : "Coder Sathi",
        "department" : "Engineering",
        "salary" : 50000,
        "address" : {
                "city" : "Kathmandu",
                "area" : "Pashupatinath"
        }
}
{
        "_id" : ObjectId("631c96a4927abfef4bc0c1e7"),
        "name" : "Virat Kohli",
        "department" : "Engineering",
        "salary" : 50000,
        "address" : {
                "city" : "Delhi",
                "area" : "Kalkaji"
        },
        "bonus" : 40000
}
{
        "_id" : ObjectId("631c96ad927abfef4bc0c1e8"),
        "name" : "Radha Krishnan",
        "department" : "HR",
        "salary" : 50000,
        "address" : {
                "city" : "Pokhara",
                "area" : "Lakeside"
        },
        "bonus" : 50000
}

With the help of the above documents, let’s see which employee has the same amount of salary and bonus. To check the result we can use the $expr operator as given below.

Query

db.employee.find({"$expr": {"$eq":["$salary", "$bonus"]}}).pretty()

And the output is:

{
        "_id" : ObjectId("631c96ad927abfef4bc0c1e8"),
        "name" : "Radha Krishnan",
        "department" : "HR",
        "salary" : 50000,
        "address" : {
                "city" : "Pokhara",
                "area" : "Lakeside"
        },
        "bonus" : 50000
}

Cool!

The query we’ve written matches the exact one employee document whose salary and the bonus are equal.

There is one thing may look strange to you is that the $ sign with the field salary and bonus.

You may ask why the $ sign is there.

The $ sign in MongoDB query language has a very important use case. One of them is to denote the use of an operator and another addresses the value of the field rather than the field name itself.

In our query, $salary and $bonus indicate that the value of these is 50000. That is the reason MongoDB Query Language (MQL) is able to compare values between these fields.

Let’s take another example where we will be finding the list of employees whose salary is greater than the salary amount with the following query:

db.employee.find({"$expr": {"$gt":["$salary", "$bonus"]}}).pretty()

And the output is:

{
        "_id" : ObjectId("62f77a41c41dfbf8c1e24e17"),
        "name" : "Coder Sathi",
        "department" : "Engineering",
        "salary" : 50000,
        "address" : {
                "city" : "Kathmandu",
                "area" : "Pashupatinath"
        }
}
{
        "_id" : ObjectId("631c96a4927abfef4bc0c1e7"),
        "name" : "Virat Kohli",
        "department" : "Engineering",
        "salary" : 50000,
        "address" : {
                "city" : "Delhi",
                "area" : "Kalkaji"
        },
        "bonus" : 40000
}

Like in the earlier query, we are able to use $expr operator with other comparing operators as well.

Conclusion

In this post, we learned to use $expr an Expressive query operator in MongoDB.