Find with string greater than

Working with Data
1

Hi

How do I find all documents where “diskSizeGb” > 200 (integer)

Document:

_id:601d08fcffc1a9cb3c104b5d
canIpForward:true
disks:0
diskSizeGb:"125"

Thanks
Felix

2

Hello

There is an aggregation operator https://docs.mongodb.com/manual/reference/operator/aggregation/toInt/

You can convert the string field to int and then compare.

If aggregation add a match stage

{"$match" : {"$expr" : {"$gt" : [{"$toInt" :"$diskSizeGb"} , 200]}}}

If find

find({"$expr" : {"$gt" : [{"$toInt" :"$diskSizeGb"} , 200]}})

*$expr allows us to use aggregate operators to query

1 Like
3

Thanks a lot, mongo is really cool - my first real work with it

diskSizeGb is part of an array, so the find query don’t quite work, see screenshot

Screenshot 2021-02-06 at 12.00.44

4

You can use map and anyTrue or allTrue,put the expression in match or find.

This keeps a document,if all disks >200 GB size

{"$expr" :
 {"$allElementsTrue" :
  {"$map" :
   {"input" : "$disks",
    "as" : "disk",
    "in" : {"$gt" : [{"$toInt" : "$$disk.diskSizeGb"} , 200]}}}}}

This keeps a document,if at least one disk >200 GB size

{"$expr" :
 {"$anyElementsTrue" :
  {"$map" :
   {"input" : "$disks",
    "as" : "disk",
    "in" : {"$gt" : [{"$toInt" : "$$disk.diskSizeGb"} , 200]}}}}}

You can do it with reduce,also,reduce in true or false.

5

Welcome to the MongoDB Community @Felix_Nielsen!

If this is going to be a common query, the most efficient approach would be to either change diskSizeGb to an integer (recommended) or to add another field which has the string value converted to an integer. The integer field should also be indexed.

You can convert from string to integer via aggregation processing with $toInt (MongoDB 4.0+) as @Takis suggested, but that approach will result in evaluating matches for every document in your collection.

An indexed range query on diskSizeGb without type conversion will only have to examine matching index keys. For more insight into how queries are processed, have a look at the Explain Results from the query planner. MongoDB Compass (a free desktop UI) has a helpful Explain Plan feature with a visual summary of the raw explain metrics.

For some great tips & tricks on understanding query performance, I also recommend watching Let’s .explain() Performance from last year’s MongoDB.live conference.

Regards,
Stennie

6

Thanks @Takis works great!

I did have some regex changing the json when I imported data, but wanted to see how it could be done without any pre-conversion.

Is there an easy way to change the string to int on the data schema? - so I can run it ones after a fresh import

Thanks again!
Felix