I have a database in which records look like this:
{
id: someId
initialValue: 100
currentValue: 150
creationDate: someDate
}
And I have to get values that are the biggest in terms of difference between currentValue
and initialValue
. Is it possible in MongoDB to write a sorting function that will substract one value from another and then compare (sort) them?
>Solution :
Sure, simply generate the desired value to sort on in a preceding $addFields
stage first:
{
$addFields: {
diff: {
"$subtract": [
"$currentValue",
"$initialValue"
]
}
}
},
{
$sort: {
diff: -1
}
}
Note that this operation cannot use an index so will have to manually sort the data. This may be a heavy and/or slow operation. You may wish to consider persisting the value when you write to the documents and index it. This would slightly increase the write overhead, but would significantly reduce the amount of work and time required to perform the reads.