CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
Sajitha Liyanage
Sajitha Liyanage

Reputation: 473

Find documents with $gte or $lte which has numbers as string values in arrays

I have a MongoDB collection that contains documents as follows:

{
 "_id": ObjectId(.....),
 "name": "John",
 "contacts": [
    {
      "name": "henry",
      "age": "22"
    },
    {
      "name": "merry",
      "age": "12"
    }
 ]
}

I want to find documents which have contacts.age is less than 20. I tried the following mongo query but had no luck. Anyone can help me out?

document.Users.find({'$expr':{'$lte': [{'$toInt':'$contacts.age'}, '20'] }})

Above query gives following error:

Query failed with error code 241 and error message 'Executor error during find command :: 
caused by :: 
Unsupported conversion from array to int in $convert with no onError value' on server

Upvotes: 1

Views: 2172

Answers (1)

J.F.
J.F.

Reputation: 15187

If you want all document (without change anything) you can try this aggregation query:

The trick here is to get only documents where there is one true in the array returned from condition "$lte": [{"$toInt": "$$this"},20].

That's mean that only will be matched documents which has an array with a value (minimum one subdocument) matched.

EDIT with the final working example, using two ways to ensure the data:

  • Using "contacts": { "$ne": null } into $match stage just before the $expr to avoid map over null.
  • Using $convert to be able to use onError and onNull in case any value is not a number and can't be converted.
db.collection.aggregate([
  {
    "$match": {
      "contacts": {
        "$ne": null
      },
      "$expr": {
        "$in": [
          true,
          {
            "$map": {
              "input": "$contacts",
              "in": {
                "$lte": [
                  {
                    "$convert": {
                      "input": "$$this.age",
                      "to": "int",
                      "onError": "",
                      "onNull": ""
                    }
                  },
                  30
                ]
              }
            }
          }
        ]
      }
    }
  }
])

Example here

Upvotes: 2

Related Questions