$expr（查询谓词操作符）

$lookup 操作中的 $expr

当 $expr 出现在属于 $lookup 子管道的 $match 阶段时， $expr 可以引用 $lookup 阶段定义的 let 变量。有关示例，请参见使用多个连接条件和关联子查询。

放置在 $expr 操作符上的 $eq、$lt、$lte、$gt 和 $gte 比较操作符可以使用 $lookup 阶段引用的 from 集合上的索引。限制：

• 索引只能用于字段和常量之间的比较，因此 let 操作数必须解析为常量。

  示例，$a 和常量值之间的比较可以使用索引，但 $a 和 $b 之间的比较不能使用索引。

• 当 let 操作数解析为空值或缺失值时，索引不用于比较。

• 不使用 多键、部分 或 稀疏 索引。

比较同一文档中的两个字段

$expr 可以包含对同一文档中的字段进行比较的表达式。

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 } } }
]

将 $expr 与条件语句结合使用

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.