$expr

$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 複数の結合条件と相関サブクエリの使用.

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:

• インデックスはフィールドと定数の比較にのみ使用できるため、let オペランドは定数に変換する必要があります。

  たとえば、$a と定数値の比較にはインデックスを使用できますが、$a と $b の比較には使用できません。

• let オペランドが空の値または欠損値に変換される場合の比較には、インデックスは使用されません。

• マルチキーインデックスは使用されません。

Compare Two Fields from a Single Document

$expr には、同じドキュメントのフィールドを比較する式を含めることができます。

これらのドキュメントを使用して monthlyBudget コレクションを作成します。

db.monthlyBudget.insertMany( [
   { _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 documents where the spent amount exceeds the budget:

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

出力:

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

Use $expr With Conditional Statements

Some queries require the ability 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.

次のドキュメントを使用してサンプルsupplies コレクションを作成します。

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 the prices such that:

• If qty is greater than or equal to 100, the discounted price will be 0.5 of the price.

• If qty is less than 100, the discounted price is 0.75 of the price.

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

The following example uses $expr with $cond to calculate the discounted price based on the qty and $lt to return documents whose calculated discount price is 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 and whether discounted price is less than NumberDecimal("5") (i.e. whether the document meets the query condition).

The db.collection.find() operation returns the documents whose calculated 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 discounted price, that price is not reflected in the returned documents. Instead, the returned documents represent the matching documents in their original state. The find operation did not return the binder or legal pad documents, as their discounted price was greater than 5.