How to use $elemMatch and $expr MongoDB?

Question

I have a collection:

 {
_id: ObjectId('5fb25b089b86a21e3fe00dc8'),
sellingPoint: [
  {
    organizationUnitId: ObjectId('5fb34ba2d5f7ad3cee5b5f6b'),
    name: 'HP',
    amount: 100,
    miximumSell: 0,
  },
  {
    organizationUnitId: ObjectId('5fb34ba2d5f7ad3cee5b5f7b'),
    name: 'HD',
    amount: 100,
    miximumSell: 200,
  },
],
},
{
_id: ObjectId('5fb25b089b86a21e3fe00dc9'),
sellingPoint: [
  {
    organizationUnitId: ObjectId('5fb34ba2d5f7ad3cee5b5f6b'),
    name: 'HP',
    amount: 100,
    miximumSell: 0,
  },
  {
    organizationUnitId: ObjectId('5fb34ba2d5f7ad3cee5b5f7b'),
    name: 'HD',
    amount: 100,
    miximumSell: 99,
  },
  ],
},
{
_id: ObjectId('5fb25b089b86a21e3fe00dc9'),
sellingPoint: [
  {
    organizationUnitId: ObjectId('5fb34ba2d5f7ad3cee5b5f6b'),
    name: 'HP',
    amount: 100,
    miximumSell: 0,
  },
  {
    organizationUnitId: ObjectId('5fb34ba2d5f7ad3cee5b5f7b'),
    name: 'HD',
    amount: 100,
    miximumSell: 150,
  },
},

I'm trying get the document have organizationUnitId: ObjectId('5fb34ba2d5f7ad3cee5b5f7b') && miximumSeel > amount.

I tried to do this but it failed:

    db.users.aggregate([
  {
    'sellingPoint':{
      $elemMatch: {
        $and:[
            { 'organizationUnitId': ObjectId('5fb34ba2d5f7ad3cee5b5f7b')},
            {$expr : 
              { $lt: ['$amount', '$miximumSell'] }
             }
        ]
      }
    }
  }
])

Answer 1

You can try,

• $filter to get matching documents from sellingPoint array as per your conditions
• $match to filter not empty result

db.collection.aggregate([
  {
    $addFields: {
      sellingPoint: {
        $filter: {
          input: "$sellingPoint",
          cond: {
            $and: [
              { $eq: ["$$this.organizationUnitId", ObjectId("5fb34ba2d5f7ad3cee5b5f7b")] },
              { $lt: ["$$this.amount", "$$this.miximumSell"] }
            ]
          }
        }
      }
    }
  },
  { $match: { sellingPoint: { $ne: [] } } }
])

Playground