'How to do fast query on String dataType in MongoDB, where values are of type double

I have a field contractValue and other fields in a collection contract which is of type String . It basically holds double value like 1200 or 1500 but at some places it may contain value like $1200 or $1500.

Sample data from collection:

{ ..
   ..
   contractValue: "1200", //This is the one stored as String. I need 
                          // to perform range query over it
   ..

   ..
 }

{ ..
   ..
   contractValue: "$1500",
   ..

   ..
 }

I have requirement where i need to fetch contracts based on contract values. Query can be like below:

{$and: [ {'contractValue': {$gt: 100}}, {'contractValue': {$lt: 1000 }}]}

This query is giving me wrong result. It is also giving me documents having contractValue like 1238999 Also I need to create indexes on contractValue

  • Is it possible to create index on contract value , so that I can efficiently make range query, so that whenever making any query, it will do < or > on Index and will fetch exact set of documents, rather than making change in schema?

  • How to handle values like $1200 in index, so index value just contain 1200 as integer rather than $1200

mongodbmongodb-querymongoose-schema


Solution 1:[1]

try this:

https://mongoplayground.net/p/TG3Y5tdh9aK

it assumes string data will be either a quoted number or a quoted number with "$" at the front

db.collection.aggregate([
  {
    $project: {
      "newContractValue": {
        "$convert": {
          "input": "$contractValue",
          "to": "double",
          "onError": {
            $toDouble: {
              "$substr": [
                "$contractValue",
                1,
                {
                  "$strLenCP": "$contractValue"
                }
              ]
            }
          }
        }
      }
    }
  },
  {
    $match: {
      $and: [
        {
          "newContractValue": {
            $gt: 100
          }
        },
        {
          "newContractValue": {
            $lt: 1000
          }
        }
      ]
    }
  }
])

This can be used to set a new contractValueNew field as number from the existing contractValue

db.getCollection('yourCollection').find({})
.forEach(function(record) {
    
    if(record.contractValue.toString().substring(0, 1) == '$') {
        record.contractValueNew = NumberInt(parseInt(record.contractValue.substring(1, record.contractValue.length)));
    } else {
        record.contractValueNew = NumberInt(parseInt(record.contractValue))
    }
    
    db.getCollection('yourCollection').save(record)
})

Solution 2:[2]

Try:

      db.collection.find({'contractValue': {$gt: 100, $lt: 1000 }})

Create index on contractValue , but convert all values as numbers ...

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2

Related Questions