Question 116270 in Mongodb Question posted in
The official documentation can be found here.

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?

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search