How do I do calculation in sort in aggreation mongoose

user_13476728 0
Viewed 112

I am trying to do this calculation but I am having some trouble. Anyone have any ideas how I can do calculation inside my aggregation? I wanna do something like this:

const test = await Test.aggregate([
    {
        $sort: {
            $divide: [
                'value',
                Math.pow(1.1, new Date() - 'date'),
            ],
        },
    },
]);

For example here, I wanna do 1.1^number of days has passed. The Test schema has a "value" of type Float and a date of type date.

mongoose mongodb
1 Answers

You have two issues here:

  1. You're trying to use javascript functions within the pipeline, while this is possible by using $function it is not recommended, especially if you can execute the same logic using Mongodb operators.

  2. $sort stage has this following structure:

{ $sort: { <field1>: <sort order>, <field2>: <sort order> ... } }

As you can tell it's not being followed in your example as you're trying to use an expression.

So how can we solve these?

Well you can use $pow instead of Math.pow, $$NOW instead of new Date() and $subtract instead of the - javascript operator. You will also need to add a "sortField" to sort by to match the $sort stage structure, all of this would look like this:

db.collection.aggregate([
  {
    "$addFields": {
      "sortField": {
        $divide: [
          "$value",
          {
            $pow: [
              1.1,
              {
                $subtract: [
                  "$$NOW",
                  "$date"
                ]
              }
            ]
          }
        ]
      }
    }
  },
  {
    $sort: {
      sortField: 1
    }
  }
])

Mongo Playground

  • Mind you subtracting dates will give you result in miliseconds, you will have to divide it by the required number ( 60 * 1000 * 60 * 24 for a day ) to get the right mesaurement.
user_107703700
Related