$expr (query predicate operator)

$expr in $lookup Operations

When $expr appears in a $match stage that is part of a $lookup subpipeline, $expr can refer to let variables defined by the $lookup stage. For an example, see Use Multiple Join Conditions and a Correlated Subquery.

The $eq, $lt, $lte, $gt, and $gte comparison operators placed in an $expr operator can use an index on the from collection referenced in a $lookup stage. Limitations:

• Indexes can only be used for comparisons between fields and constants, so the let operand must resolve to a constant.

  For example, a comparison between $a and a constant value can use an index, but a comparison between $a and $b cannot.

• Indexes are not used for comparisons where the let operand resolves to an empty or missing value.

• Multikey, partial, or sparse indexes are not used.

Compare Two Fields from a Single Document

$expr can contain expressions that compare fields from the same document.

The following operation uses $expr to find documents in the movies collection where the Rotten Tomatoes viewer rating exceeds the critic rating:

db.movies.find(
   { $expr: { $gt: [ "$tomatoes.viewer.rating", "$tomatoes.critic.rating" ] } },
   { _id: 0, title: 1, "tomatoes.viewer.rating": 1, "tomatoes.critic.rating": 1 }
).sort( { "tomatoes.viewer.rating": -1 } ).limit( 3 )

[
  { title: 'I Am Maria', tomatoes: { viewer: { rating: 5 } } },
  { title: 'Kadin Hamlet', tomatoes: { viewer: { rating: 5 } } },
  { title: 'The Seine Meets Paris', tomatoes: { viewer: { rating: 5 } } }
]

Use $expr With Conditional Statements

Some queries need to execute conditional logic when defining a query filter. The aggregation pipeline provides the $cond operator to express conditional statements. By using $expr with the $cond operator, you can specify a conditional filter for your query statement.

Assume you want to calculate a weighted score for movies so that highly-rated movies with few votes do not dominate the results:

• If imdb.votes is greater than or equal to 1000, the weighted score is the full imdb.rating.

• If imdb.votes is less than 1000, the weighted score is 0.5 of the imdb.rating.

You would like to know which movies in the movies collection have a weighted score greater than 9.

The following example uses $expr with $cond to calculate the weighted score based on imdb.votes and $gt to return documents whose calculated weighted score is greater than 9:

db.movies.find(
   {
      "imdb.rating": { $type: "number" },
      "imdb.votes": { $type: "number" },
      $expr: {
         $gt: [
            {
               $cond: {
                  if: { $gte: ["$imdb.votes", 1000] },
                  then: { $multiply: ["$imdb.rating", 1.0] },
                  else: { $multiply: ["$imdb.rating", 0.5] }
               }
            },
            9
         ]
      }
   },
   { _id: 0, title: 1, "imdb.rating": 1, "imdb.votes": 1 }
).sort( { title: 1 } ).limit(5)

The following table shows the weighted score for selected documents and whether the weighted score is greater than 9 (i.e. whether the document meets the query condition).

The db.collection.find() operation returns 5 documents whose calculated weighted score is greater than 9:

[
  { title: 'Band of Brothers', imdb: { rating: 9.6, votes: 183802 } },
  { title: 'Baseball', imdb: { rating: 9.1, votes: 2460 } },
  { title: 'Cosmos', imdb: { rating: 9.3, votes: 17174 } },
  { title: 'Frozen Planet', imdb: { rating: 9.2, votes: 5903 } },
  { title: 'Human Planet', imdb: { rating: 9.2, votes: 9057 } }
]

Even though $cond calculates a weighted score, that score is not reflected in the returned documents. Instead, the returned documents represent the matching documents in their original state.