MongoDB: Using $expr to query using aggregation expressions

Introduction

In this tutorial, we will explore MongoDB’s powerful $expr operator which allows you to use aggregation expressions within the query language. This can significantly extend the capabilities of MongoDB’s query operations, enabling more complex conditions and computations.

Before diving into $expr, let’s briefly revise the concept behind aggregation in MongoDB. Aggregation operations process data records and return computed results. Aggregation uses a pipeline approach where the output of one stage becomes the input for the next one. This is often used for grouping, sorting, and summarizing data.

Working with the $expr Operator

The $expr operator allows the use of aggregation expressions in query operations. By using $expr, you can build query predicates that are more complex than simple equality checks. This includes using variables, conditional expressions, math operations, and other functions that are available within the aggregation framework.

Basic usage: The $expr operator can be employed in many MongoDB methods, including find(), update(), and even delete() operations.

db.collection.find({ $expr: { <expression> } })

Getting Started With $expr

For our first example, let’s consider a collection employees where each document has a salary and a bonus field:

{
  "_id": 1,
  "name": "John Doe",
  "salary": 70000,
  "bonus": 5000
}

Suppose we want to find employees where the bonus is more than 5% of their salary – a condition that would require an arithmetic operation:

db.employees.find({
  $expr: {
    $gt: [
      "$bonus",
      { $multiply: ["$salary", 0.05] }
    ]
  }
})

The $gt operator inside $expr is comparing the bonus field with 5% of the salary field for each document. The output of this operation would return all the documents where the condition is met.

Complex Queries Using $expr

Now let’s take a look at a more advanced use case. Imagine another collection projects that contains documents like:

{
  "_id": 1,
  "name": "Project X",
  "budget": 500000,
  "expenses": 450000,
  "profit": 100000
}

We might be interested in finding projects where the actual profit differs from the projected profit by more than a certain threshold. For instance, where the difference between profit and the subtracted value of budget from expenses is greater than 20000. This could look like:

db.projects.find({
  $expr: {
    $gt: [
      { $abs: { $subtract: ["profit", { $subtract: ["budget", "expenses"] }]} },
      20000
    ]
  }
})

In this complex statement, we are using $subtract to find the difference between the budget and expenses and then calculating the absolute value of the difference with the actual profit using $abs. The result is then compared to 20000 using $gt.

Joining Collections with $expr

MongoDB’s $lookup stage of the aggregation pipeline allows you to join documents from another collection using a specified condition in the form of an $expr expression. This join-like capability is particularly useful when you need to match fields from different collections that aren’t necessary equal but fulfill a given condition.

Consider two collections: orders and products. The goal is to find all orders where the order amount is more than the stock of the respective product(s). As an example:

db.orders.aggregate([
  {
    $lookup: {
      from: "products",
      let: { order_qty: "$quantity", product_code: "$product_code" },
      pipeline: [
        { $match:
          { $expr:
            { $and: [
              { $eq: ["$code", "$product_code"] },
              { $lt: ["$stock", "$order_qty"] }
            ]}
          }
        }
      ],
      as: "stock_issues"
    }
  },
  { $match: { stock_issues: { $ne: [] } } }
])

In the code above, $lookup brings together data from orders and products collections. It finds orders where the order quantity is greater than the product’s stock using the $expr.

Using Variables and Conditional Statements in $expr

MongoDB allows the usage of variables within $expr along with the ability to execute conditional statements. Take, for example, that you only want to apply a certain discount on items that exceed a certain sales threshold.

db.products.find({
  $expr: {
    $cond: {
      if: { $gt: ["$sales", 1000] },
      then: { $lt: ["$price", { $subtract: ["$price", "$discount"] }] },
      else: { $eq: ["$price", "$price"] }
    }
  }
})

In this query, the $cond operator is being used to apply a conditional statement within $expr. It decreases the price by the discount if the sales exceed 1000 units. Otherwise, the price remains unchanged.

Conclusion

This tutorial has introduced the $expr operator and provided examples of how to enhance your queries in MongoDB. By adopting $expr in your MongoDB operations, you can efficiently manage complex analysis and data manipulation that might otherwise require additional processing steps or application-level code.