How can I compare two fields of a subdocument that exists within an array

Question

How can I match a document whose property A - an array of embedded documents - includes one such as subDocument.propertyX < subDocument.propertyY? Tried:

'A': {
    $elemMatch: {
        propertyX: {
            $lt: '$propertyY',
        },
    },
},

MongoDB Playground

Answer 1

1. you could do something like below. $filter the A subdoc array that satisfy the condition and get the $size of that array. If the size of that array is greater than ($gt) 0 it means there is at least 1 that satisfies the condition subDocument.propertyX < subDocument.propertyY. Also $expr allows the use of aggregation expressions within the query language.

db.collection.find({
  $expr: {
    $gt: [
      {
        $size: {
          $filter: {
            input: "$A",
            as: "subDocument",
            cond: { $lt: [ "$$subDocument.propertyX", "$$subDocument.propertyY" ] }
          }
        }
      },
      0
    ]
  }
})

playground

2. another way of doing using $map and $anyElementTrue in an aggregation pipeline.

db.collection.aggregate([
  {
    $addFields: {
      hasMatch: {
        $anyElementTrue: {
          $map: {
            input: "$A",
            as: "subDocument",
            in: { $lt: [ "$$subDocument.propertyX", "$$subDocument.propertyY" ] }
          }
        }
      }
    }
  },
  { $match: { hasMatch: true } },
  { $project: { hasMatch: 0 } }
])

playground