Is there a way to sort MongoDB records by the highest difference between two values in an object?

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
    }
  }

Playground example here

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.

Leave a Reply