CODEWITHSUNDEEP
arraysmongodb
RetroCoder
RetroCoder

Reputation: 2685

How to find range of array is greater than or equal to its integer equivalent

I'm Using MongoDB 6:

As an example, I want to check for a number that is greater than 1 and less than 3 in an array that contains both numbers as integers, and numbers as string.

Here is the example dataset:

> db.test.find()
{ "_id" : ObjectId("62ed3cfbeadf50344d622dd0"), "test" : 1, "name" : "Testing Alpha", "array" : [ 1, 2, 3 ] }
{ "_id" : ObjectId("62ed4798eadf50344d622dd1"), "array" : [ "1", "2" ] }
{ "_id" : ObjectId("62ed47f5eadf50344d622dd2"), "array" : [ "1111", "22242" ] }
{ "_id" : ObjectId("62ed4826eadf50344d622dd3"), "array" : [ "11119", "222438", "31738234", "15", "41", "141" ] }

I'm trying to find just the number 2 or a number between 1 and 3 included items in quotes and regular integers. How do I get all arrays that contain 2 or "2" using the $gt and $lt operators.

So basically I'm trying to find 2 or "2" in array field, but I need to convert the string to an int and don't know how to do that. So only the first two items in the find should be found.

Here are my failed finds:

db.test.find({array: {$gt: "1", $lt: "3"}})
db.test.find({array: {$gt: 1, $lt: 3}})
db.test.find({$and: [array: {$gt: 1, $lt 3}, array: {$gt: "1", $lt "3"}]})
db.test.find({$or: [{"array": {$gt: 1, $lt: 3}}, {"array": {$gt: "1", $lt: "3"}}]})
db.test.find({$and: [{"array": {$gt: 1, $lt: 3}}, {"array": {$gt: "1", $lt: "3"}}]})

Upvotes: 0

Views: 46

Answers (1)

nimrod serok
nimrod serok

Reputation: 16033

One option is using an aggregation pipeline with $filter and $toInt:

db.collection.aggregate([
  {$set: {
      arrayInt: {
        $filter: {
          input: "$array",
          cond: {
            $and: [
              {$lt: [{$toInt: "$$this"}, 3]},
              {$gt: [{$toInt: "$$this"}, 1]}
            ]
          }
        }
      }
    }
  },
  {$match: {"arrayInt.0": {$exists: true}}},
  {$unset: "arrayInt"}
])

See how it works on the playground example

Upvotes: 1

Related Questions