mongoDB sort on two fields

Question

I have a mongodb document something like below :

 {

        "match_key" : 44801,
        "total_points" : 10.00,


    },
    {
        "match_key" : 44901,
        "total_points" : 8.00,
    },
   {
        "match_key" : 44901,
        "total_points" : 7.00,
    },

   {
        "match_key" : 44901,
        "total_points" : 11.00,
    },
    {

    "match_key" : 44801,
    "total_points" : 7.00,


}

I want to sort the above docs ,but want to keep the documents with same match_key together.

Actual result :

 {
        "match_key" : 44901,
        "total_points" : 11.00,
    }
,
 {

        "match_key" : 44801,
        "total_points" : 10.00,


    },
{
        "match_key" : 44901,
        "total_points" : 8.00,
    },

{

    "match_key" : 44801,
    "total_points" : 7.00,


}

 {
        "match_key" : 44901,
        "total_points" : 6.00,
    },

But you see that same match keys are not together.

Expected result :

{
        "match_key" : 44901,
        "total_points" : 11.00,
    },
{
        "match_key" : 44901,
        "total_points" : 8.00,
    },
{
        "match_key" : 44901,
        "total_points" : 7.00,
    },
{

        "match_key" : 44801,
        "total_points" : 10.00,


    },
{

        "match_key" : 44801,
        "total_points" : 6.00,


    },

Such that docs with same_match key are in descending order.

This is what I tried doing :

 db.collection.aggregate(
    [
      { $match: { match_key:{$in:[44801,45910]}}},
      { $sort: {total_points:1,match_key:1} }
    ]
 )

This is some sql equivalent of the question ,I am trying to ask -Sort by most recent but keep together by another ID column

My main motive here is to sort the docs and keep the docs with same match_key together but in order /**

user_7237613 · Answer

You need to split this $sort into two stages like below :

db.collection.aggregate([
  {
    $match: {
      match_key: {
        $in: [
          44801,
          45910
        ]
      }
    }
  },
  {
    $sort: {
      total_points: -1
    }
  },
  {
    $sort: {
      match_key: -1
    }
  }
])

Test : MongoDB-Playground

Note : As we've $match as first stage, we should be good to do sorting, but even though as we need to do sort twice, try to keep your dataset as low as possible, You need to have two Single-field-index on two of your fields total_points & match_key.

Issue : So when you do this : { $sort: {total_points:-1,match_key:1} } your query is sorting on total_points :1 field first & if it finds two docs with same total_points value then it would go to match_key:1 will sort those two docs to keep them in order.

Example of issue : Using compound sorting like above you would get below result

  {
    "_id": ObjectId("5a934e000102030405000002"),
    "match_key": 44801,
    "total_points": 10
  },
  {
    "_id": ObjectId("5a934e000102030405000003"),
    "match_key": 45910,
    "total_points": 10
  },
  {
    "_id": ObjectId("5a934e000102030405000005"),
    "match_key": 44801,
    "total_points": 7
  }

If you see total_points are sorted in order, with first priority is on total_points & since there are two docs with "total_points": 10 it kicks in second priority of match_key. Anyway if you want to sort like 11->10->7 you need to use descending mechanism with -1 but not 1 (which is ascending).

mongoDB sort on two fields

1 Answers