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

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

CodePudding user response：

Try:

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

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

CodePudding user response：

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
          }
        }
      ]
    }
  }
])