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

Saying "it fails in some ways" is vague and unhelpful. What's the expected behavior and what are you seeing instead? Please describe and provide a simple example. — B. Fleming
– B. Fleming, Commented Oct 15, 2018 at 0:44

IftekharDani · Accepted Answer · 2018-10-16 05:09:56Z

2

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

edited Oct 16, 2018 at 5:09

answered Oct 15, 2018 at 5:57

IftekharDani

3,7191 gold badge18 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Patrickkx Over a year ago

How to use it with mongoose?

Collectives™ on Stack Overflow

mongodb / mongoose - sort by two fields

Expected result:

Current solution:

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Expected result:

Current solution:

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related