mongodb / mongoose - sort by two fields

Question

I have two fields:

• date (ISODate)
• value (it's a number - however can be also undefined)

---

I want to sort them in a specific way.

Firstly - sort by value. If it exists (not undefined), let them come first. If value is undefined, push these objects at the end.

Secondly - sort by date. So at this moment, we will have everything sorted by value. But now I want to sort it by date.

Expected result:

[
   {
      value: 1, 
      date: today, // ofc it's a date but just to represent the case
   }, 
   {
      value: 2,
      date: yesterday,
   },
   {
      value: undefined,
      date: today,
   },
   {
      value: undefined,
      date: yesterday,
   }
]

Current solution:

.sort({
  value: -1,
  date: -1
});

However it fails in some situations, unfortunately Im unable to detect why it sometimes sort it in a wrong way. But it does...

Thank you in advance

Answer 1

To achieving this you can use aggregate with $cond.

db.getCollection('collectionName').aggregate([
{
   $project :
       {
           "id" : 1,
           "value" : 1,
           "date": 1,
           sortValue: {
            $cond: {
              if: { $eq: ['$value',undefined ]},
              then: 0, 
              else: 1,
            },
          }
       }
},
{
   $sort :
       {
           "sortValue" :-1,
           "value" : 1,
           "date" : -1
       }
}])

Tested in Mongo GUI.

Aggregate In Mongoose:

Model.aggregate([
  {
    $project:
    {
      "id": 1,
      "value": 1,
      "date": 1,
      sortValue: {
        $cond: {
          if: { $eq: ['$value', undefined] },
          then: 0,
          else: 1,
        },
      }
    }
  },
  {
    $sort:
    {
      "sortValue": -1,
      "value": 1,
      "date": -1
    }
  }
], function (err, result) {
  if (err) {
    console.log(err);
    return;
  }
  console.log(result);
});