Convert String type to Number type when querying on MongoDB

user_12528974 0
Viewed 148

I have a MongoDB Structure like this.

[
  {
    _id: 1,
    data: [ 
      { price : "2", title: "title-1" }
    ]
  },
  {
    _id: 2,
    data: [ 
      { price : "2.0", title: "title-2" }
    ]
  },
  {
    _id: 3,
    data: [ 
      { price : "2.00", title: "title-3" },
      { price : "2.99", title: "title-4" }
    ]
  }

I want to write a query where I can bring in all the titles which have a price value equal to 2(Number Type). But the issue is, the price field is of type String. And when I will query I have to put various conditions for price like:-

db.collection("test").find({ '$or': ["'data.price": '2'}, {"data.price": '2.0'}, {"data.price": '2.00'}] })

Is there a way in MongoDB where we can say that we want to convert a type before querying? OR Is there a way we can write the above query in a better way?

aggregation-framework mongodb nosql find
1 Answers

Ideally you should validate the input parameters for your collection so that you keep your data types consistent, especially it is beneficial to keep numeric data as numbers rather than strings,

If you want to convert existing items before querying you need to use $map along with $toDouble. After such conversion you can run your query:

db.collection.aggregate([
    {
        $project: {
            data: {
                $map: {
                    input: "$data",
                    in: {
                        $mergeObjects: [
                            "$$this",
                            { price: { $toDouble: "$$this.price" } }
                        ]
                    }
                }
            }
        }
    },
    {
        $match: { "data.price": 2 }
    }
])

Mongo Playground

user_62389770
Related