Comparing versions in MongoDB

Question

just wondering if anyone can come up with a smarter way to do this...

Let's say I have some documents in MongoDB with a version property, for example something like { ..., version: { major: 1, minor: 2, patch: 13 } }, or { ..., version: "1.2.13" } if it is easier.

What's the easiest way to find all documents that have version X.Y.Z or above? The way I'm doing it now is basically a huge $and clause.

Answer 1

Your first design is actually very good. The reason is because you can index the fields major, minor and patch for fast reads.

Querying the data is actually much easier than using the $and query. You can just use a basic query on matching fields:

e.g.

db.versions.find({major:1,minor:2,patch:20}) //give me documents of version 1.2.20
db.versions.find({major:1}) //give me all documents with major version 1
db.versions.find({major:1,minor:{$gte:2,$lte:5}}) //give me all documents of major version and minor versions between 2 and 5 inclusive.

You can create an index like

db.versions.ensureIndex({major:1,minor:1,patch:1})

The $and query is mainly for the case where you want to run a more complex query on the same field multiple times. For instance, if you want to get major versions 1 and 2, you can use $and.

db.versions.find({major:1,major:2}) will actually only return documents with major version 2. However, db.versions.find({a:$all:[1,2]}) would work too in this case. You should ideally avoid using $and when you can because it actually spawns multiple queries for each expression in the $and array and performs a union. This is much more expensive than the query samples I provided above.

Answer 2

If you use a fixed number of digits for the version number and store it as a string, you should be able to use $gte.

"001.002.0013" > "001.002.0011" = true
"001.003.0013" > "001.002.0013" = true
"002.000.0000" > "001.002.0013" = true
"001.002.0012" > "001.002.0013" = false
"001.001.0013" > "001.002.0013" = false