MongoDB match document's array element based on condition defined in the element's value-CodePudding

I have a MongoDB (v6.0.3) document in my database:

{
  "user": "123",
  "alerts": [
    {
      "alertDirection": "$gte",
      "alertValue": 9999,
      "location": "home",
      "available": false,
      "position": 28
    },
    {
      "alertDirection": "$lte",
      "alertValue": 50,
      "location": "home",
      "available": true,
      "position": 14
    },
    {
      "alertDirection": "$gte",
      "alertValue": 100,
      "location": "home",
      "available": true,
      "position": 71
    },
    {
      "alertDirection": "$gte",
      "alertValue": 100,
      "location": "out",
      "available": true,
      "position": 43
    }
  ]
}

And I'm matching array elements I need based on few conditions:

{
  $match: {
    "alerts": {
      $elemMatch: {
        $and: [
          { "location": "home" },
          { "available": true }
        ]
      }
    }
  }
}

Is it possible to expand my $match to fetch only documents with alerts elements matching alertValue based on alertDirection value and a variable I have?

For example, I have a variable myValue with value 10. I'd like to match only elements in array where myValue is either higher or lower than alertValue, depending on alertDirection. In my example, the only match would be the following element (because 10 is lower or equal than 50):

{
  "alertDirection": "$lte",
  "alertValue": 50,
  "location": "home",
  "available": true,
  "position": 14
}

Or if myValue would be 100, only the following element would be matched (because 100 is greater or equal than 100):

{
  "alertDirection": "$gte",
  "alertValue": 100,
  "location": "home",
  "available": true,
  "position": 71
}

CodePudding user response：

You can try this query:

The trick here is to use $cond and if the "direction" is $gte then compare with $gte otherwise $lte (but be careful to use $literal to get the string literal.

And the last step is a group to get result into an array in the same way that are into DB but is optional.

db.collection.aggregate([
  {
    "$unwind": "$alerts"
  },
  {
    "$match": {
      "alerts.location": "home",
      "alerts.available": true,
      "$expr": {
        "$cond": {
          "if": {
            "$eq": ["$alerts.alertDirection",{"$literal": "$gte"}]
          },
          "then": {"$gte": [yourValue,"$alerts.alertValue"]},
          "else": {"$lte": [yourValue,"$alerts.alertValue"]}
        }
      }
    }
  },
  {
    "$group": {
      "_id": "$_id",
      "user": {
        "$first": "$user"
      },
      "alerts": {
        "$push": "$alerts"
      }
    }
  }
])

Example here