Convert string to double and compare with in array of objects

Here is my mongo sample collection,

{
   "_id" : ObjectId("62aeb8301ed12a14a8873df1"),
   "Fields" : [ 
    {
        "FieldId" : "name",
        "Value" : [ "test_123" ]
    }, 
    {
        "FieldId" : "mobile",
        "Value" : [ "123" ]
    }, 
    {
        "FieldId" : "amount",
        "Value" : [ "300" ]
    }, 
    {
        "FieldId" : "discount",
        "Value" : null
    }
  ]
 }

Here I want to get the matched record like "Fields.FieldId" should be equal to "amount" and "Fields.Value.0" should be greater than 0 or something else given.

Please note down below points,

• "Fields.Value" might null too
• Sometime some of the fields may not appear in the array also

I have tried like below which is not working,

db.form.aggregate([
{
  $match:
  {
    {
       $expr: {
          $ne: [
             { $filter: { 
                input: '$Fields', 
                  cond: { if: {$eq:["$$this.FieldId", "amount"]}, 
                          then:{$gte: [{$toDouble: "$$this.Value.0"}, 0]} }
                  } 
               }, []
           ]
         }
      }
    }
}])

I don't want to use $project or $addFields. I just want to do direct $match query. Please suggest me if it is possible.

Thanks in advance, Mani

One of the canonical ways to perform element-wise checking on an array field would be using $anyElementTrue. You can first use $map to apply your condition(s) on the array to project a boolean array and apply $anyElementTrue on it.

1. use $map to iterate through the Fields array
2. check if the FieldId is equal to amount
3. try to $convert the string value into double.
   • if convert succeed, we keep the double value
   • if convert failed, says it is non-number, we fallback to default value of 0.0(or any value that is less than zero). So this query won't throw an exception even if your data does not have a value that is parseable into double.
4. chain up the condition of 2 and 3 in an $and and compare with 0. If the conversion failed, it will fall back to 0.0 and will not be selected.

db.collection.aggregate([
  {
    "$match": {
      $expr: {
        "$anyElementTrue": {
          "$map": {
            "input": "$Fields",
            "as": "f",
            "in": {
              "$and": [
                {
                  "$eq": [
                    "amount",
                    "$$f.FieldId"
                  ]
                },
                {
                  $gt: [
                    {
                      "$convert": {
                        "input": {
                          "$first": "$$f.Value"
                        },
                        "to": "double",
                        "onError": 0.0,
                        "onNull": 0.0
                      }
                    },
                    0
                  ]
                }
              ]
            }
          }
        }
      }
    }
  }
])

Mongo Playground