MongoDB queries - $condition by element in array

Question

I have the following aggregation pipeline consisting of a single $redact step: (which is supposed to return all products for which the recorded number of sales exceeds the stock)

$redact:
{
  $cond: [
    { $gt: ["$sales", "$productInfo.0.stock"] },
    "$$KEEP",
    "$$PRUNE"
  ]
}

(the syntax above is specific to Compass, so no issues here)

Where the entities look something like:

{
   _id: 123,
   sales: 60,
   price: 80,
   productInfo: [
      {
         stock: 100
      }
   ]
}

However, the query above does not seem to work. My presumption is that the issue is caused by the comparison with $productInfo.0.stock, as replacing it with another direct attribute of the entity (e.g. price) runs without any issues.

How should such a query ($cond by $gt where one of the values is from an array/list) be specified?

Answer 1

The productionInfo.0.stock syntax is the Mongo Query Language (MQL), which can be used in a $match or a find.

That particular construct is not availabl when using aggregation syntax, such as "$fieldName". Instead, you need to use $arrayElemAt, but that unfortunately doesn't support accessing fields in the reference element.

To get what you want, you will need to add a field in a prior stage that retrieves the desired element from the array, reference that object in the $redact, and then project out the temporary field, such as:

{$addFields: {firstProduct: {$arrayElemAt: [ "$productInfo", 0 ]}}},
{$redact:{
  $cond: [
    { $gt: ["$sales", "$productInfo.0.stock"] },
    "$$KEEP",
    "$$PRUNE"
  ]
}},
{$project: {firstProduct: 0}}