Sorting and ranking documents based on key values?

Question

I am trying to pull out documents with highest mark for a student in a collection and formed a query below:

{ name: "Person1", marks: 20  }
{ name: "Person2", marks: 20  }
{ name: "Person1", marks: 30  }
{ name: "Person1", marks: 25  }
{ name: "Person2", marks: 50  }
{ name: "Person1", marks: 90  }
{ name: "Person3", marks: 990 }

An my query:

db.mytest1.aggregate( [          
  { $sort : { "name" : 1,"marks" : -1} },
  {$group:
    {
      _id: "$name",
      name: { $first: "$name" },
      marks: { $first: "$marks" }
   }}
])

Is there a better way to do this?

And if my scenario was to number the documents based on marks, how can I achieve it?

I'd like to get the following result:

{ name: "Person1", marks: 90,  rank: 1 }
{ name: "Person1", marks: 30,  rank: 2 }
{ name: "Person1", marks: 25,  rank: 3 }
{ name: "Person1", marks: 20,  rank: 4 }
{ name: "Person2", marks: 50,  rank: 1 }
{ name: "Person2", marks: 20,  rank: 2 }
{ name: "Person3", marks: 990, rank: 3 }

Answer 1

Starting in Mongo 5, it's a perfect use case for the new $setWindowFields aggregation operator:

// { name: "Person1", marks: 20 }
// { name: "Person2", marks: 20 }
// { name: "Person1", marks: 30 }
// { name: "Person1", marks: 25 }
// { name: "Person2", marks: 50 }
// { name: "Person1", marks: 90 }
// { name: "Person3", marks: 990 }
db.collection.aggregate([
  { $setWindowFields: {
    partitionBy: "$name",
    sortBy: { marks: -1 },
    output: { rank: { $denseRank: {} } }
  }}
])
// { name: "Person1", marks: 90,  rank: 1 }
// { name: "Person1", marks: 30,  rank: 2 }
// { name: "Person1", marks: 25,  rank: 3 }
// { name: "Person1", marks: 20,  rank: 4 }
// { name: "Person2", marks: 50,  rank: 1 }
// { name: "Person2", marks: 20,  rank: 2 }
// { name: "Person3", marks: 990, rank: 1 }

For each name (partitionBy: "$name"), this:

• sorts documents by decreasing order of marks: sortBy: { marks: -1 }
• and adds the rank field in each document (output: { rank: { $denseRank: {} } })
  • which is the rank of the document in its partition: rank: { $denseRank: {} }.
    • depending on what you want the rank to be when there are duplicate marks, you can choose between $denseRank and $rank.
  • on a specified span of documents (the window)
    • which is in our case all documents within the partition (their is a window parameter that we ignored as its default value is what we need here).