Mongodb Question posted in
The official documentation can be found here.

Mongodb – 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

Tags:

2

Answers


  1. 0

    1. Use the $and operator to match multiple conditions instead of if and then.

    2. Use the $first operator to get the first element from the array.

    3. Also, you have additional braces {} in the $match stage that break the query. Remove the additional braces {}.

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

    Demo @ Mongo Playground

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search