$expr

Definition

New in version 3.6.

• $expr
• Allows the use of aggregation expressions within the query language.

$expr has the following syntax:

{ $expr: { <expression> } }

The arguments can be any valid aggregation expression. For more information, seeExpressions.

Behavior

$expr can build query expressions that compare fieldsfrom the same document in a $match stage.

If the $match stage is part of a $lookup stage,$expr can compare fields using let variables. SeeSpecify Multiple Join Conditions with $lookup for an example.

$expr does not support multikey indexes.

Examples

Compare Two Fields from A Single Document

Consider an monthlyBudget collection with the following documents:

{ "_id" : 1, "category" : "food", "budget": 400, "spent": 450 }
{ "_id" : 2, "category" : "drinks", "budget": 100, "spent": 150 }
{ "_id" : 3, "category" : "clothes", "budget": 100, "spent": 50 }
{ "_id" : 4, "category" : "misc", "budget": 500, "spent": 300 }
{ "_id" : 5, "category" : "travel", "budget": 200, "spent": 650 }

The following operation uses $expr to find documentswhere the spent amount exceeds the budget:

db.monthlyBudget.find( { $expr: { $gt: [ "$spent" , "$budget" ] } } )

The operation returns the following results:

{ "_id" : 1, "category" : "food", "budget" : 400, "spent" : 450 }
{ "_id" : 2, "category" : "drinks", "budget" : 100, "spent" : 150 }
{ "_id" : 5, "category" : "travel", "budget" : 200, "spent" : 650 }

Using $expr With Conditional Statements

Some queries require the ability to execute conditional logic whendefining a query filter. The aggregation framework provides the$cond operator to express conditional statements. By using$expr with the $cond operator, you canspecify a conditional filter for your query statement.

Create a sample supplies collection with the following documents:

db.supplies.insertMany([
   { "_id" : 1, "item" : "binder", "qty" : NumberInt("100"), "price" : NumberDecimal("12") },
   { "_id" : 2, "item" : "notebook", "qty" : NumberInt("200"), "price" : NumberDecimal("8") },
   { "_id" : 3, "item" : "pencil", "qty" : NumberInt("50"), "price" : NumberDecimal("6") },
   { "_id" : 4, "item" : "eraser", "qty" : NumberInt("150"), "price" : NumberDecimal("3") },
   { "_id" : 5, "item" : "legal pad", "qty" : NumberInt("42"), "price" : NumberDecimal("10") }
])

Assume that for an upcoming sale next month, you want to discount theprices such that:

• If qty is greater than or equal to 100, the discounted price willbe 0.5 of the price.
• If qty is less than 100, the discounted price is 0.75 of theprice.

Before applying the discounts, you would like to know which items in thesupplies collection have a discounted price of less than 5.

The following example uses $expr with $cond tocalculate the discounted price based on the qty and$lt to return documents whose calculated discount priceis less than NumberDecimal("5"):

// Aggregation expression to calculate discounted price
 
let discountedPrice = {
   $cond: {
      if: { $gte: ["$qty", 100] },
      then: { $multiply: ["$price", NumberDecimal("0.50")] },
      else: { $multiply: ["$price", NumberDecimal("0.75")] }
   }
};
 
// Query the supplies collection using the aggregation expression
 
db.supplies.find( { $expr: { $lt:[ discountedPrice,  NumberDecimal("5") ] } });

The following table shows the discounted price for each document andwhether discounted price is less than NumberDecimal("5") (i.e.whether the document meets the query condition).

The db.collection.find() operation returns the documents whosecalculated discount price is less than NumberDecimal("5"):

{ "_id" : 2, "item" : "notebook", "qty": 200 , "price": NumberDecimal("8") }
{ "_id" : 3, "item" : "pencil", "qty": 50 , "price": NumberDecimal("6") }
{ "_id" : 4, "item" : "eraser", "qty": 150 , "price": NumberDecimal("3") }

Even though $cond calculates an effective discountedprice, that price is not reflected in the returned documents. Instead,the returned documents represent the matching documents in theiroriginal state. The find operation did not return the binderor legal pad documents, as their discounted price was greater than5.