CODEWITHSUNDEEP
mongodb
user1592380
user1592380

Reputation: 36337

Filtering dollar values with mongodb

enter image description here

I'm using mongodb and I want to select objects with AppraisedValueDisplay between 2 dollar values. I have tried filtering by :

{AppraisedValueDisplay: {$gt: 5000,$lt: 50000}}

This gives no results : I then realized that (screenshot), The value is saved in a string format like:

$35,000

How do I filter by dollar values in mongodb?

Upvotes: 0

Views: 314

Answers (2)

Umer Abbas
Umer Abbas

Reputation: 1876

You should consider saving data in a format that you usually need to retrive as other users said in the comments. But for this specific usecase you can use aggregate query to filter the results.

First convert the value into double and then match, I tested this query with mongodb version 4.0.23

db.tempCol.aggregate([
    {
        '$addFields':{
            'signRemoved': {
                '$reduce': {
                    'input': { '$split': [ '$AppraisedValueDisplay', { $literal: '$' } ] },
                    'initialValue': '',
                    'in': {
                        '$concat': [
                            '$$value',
                            {'$cond': [{'$eq': ['$$value', '']}, '', ', ']}, 
                            '$$this'
                        ]
                    }
                }
            },
        }
    },
    {
        '$addFields':{
            'commaRemoved': {
                '$reduce': {
                    'input': { '$split': [ '$signRemoved', ',' ] },
                    'initialValue': '',
                    'in': {
                        '$concat': [
                            '$$value',
                            {'$cond': [{'$eq': ['$$value', '']}, '', '']}, 
                            '$$this'
                        ]
                    }
                }
            },
        }
    },
    {
        '$addFields':{
            matchPrice: { '$toDouble': '$commaRemoved' }
        }
    },
    {
        '$match':{
            matchPrice: { '$gt': 5000, '$lt': 50000}
        }
    },
])

Upvotes: 1

codemonkey
codemonkey

Reputation: 7915

I would also like to preface this by saying that storing numeric values in your database formatted for presentation as strings is a bad idea, which you no doubt already know.

With that out of the way, here is the aggreagation you're looking for:

db.collection.aggregate([
  {
    "$project": {
      "AppraisedValueDisplay": {
        $replaceAll: {
          input: "$AppraisedValueDisplay",
          find: {
            $literal: "$"
          },
          replacement: ""
        }
      }
    }
  },
  {
    "$project": {
      "AppraisedValueDisplay": {
        "$toInt": {
          $replaceAll: {
            input: "$AppraisedValueDisplay",
            find: ",",
            replacement: ""
          }
        }
      }
    }
  },
  {
    $match: {
      AppraisedValueDisplay: {
        $gt: 30000,
        $lt: 40000
      }
    }
  }
])

The idea is to replace the $ and , with empty strings and then cast the resulting strings to integers. From that point, it's just a simple matter of matching the numeric values. Playground: https://mongoplayground.net/p/YU65M-q1QCM

Upvotes: 1

Related Questions