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?

CodePudding user response：

One of the slightly overkill but extensible solution is to use $setWindowFields to $rank the $subtract sum. You can manipulate the rank field by setting other functions if changes needed to be made in future. The out-of-the-box behaviour also provides a tie-breaker functionality.

db.collection.aggregate([
  {
    "$addFields": {
      "diff": {
        "$subtract": [
          "$currentValue",
          "$initialValue"
        ]
      }
    }
  },
  {
    "$setWindowFields": {
      "partitionBy": null,
      "sortBy": {
        "diff": -1
      },
      "output": {
        "rank": {
          $rank: {}
        }
      }
    }
  },
  {
    $sort: {
      rank: 1
    }
  },
  // cosmetics
  {
    "$unset": [
      "diff",
      "rank"
    ]
  }
])

Mongo Playground

CodePudding user response：

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.