How to get item ranking in list sorted by multiple fields in Mongoose

In Mongoose, to obtain rankings of items in a list sorted by multiple fields, utilize MongoDB's Aggregation Pipeline. Below are the detailed steps and examples:

Step 1: Define the Data Model

First, consider a User model with score and age fields that we intend to sort by.

javascript
const mongoose = require('mongoose');
const { Schema } = mongoose;

const userSchema = new Schema({
  name: String,
  score: Number,
  age: Number
});

const User = mongoose.model('User', userSchema);

Step 2: Sorting and Adding Rankings Using the Aggregation Pipeline

Apply MongoDB's $sort for sorting and $group with $push to create a ranked list.

javascript
User.aggregate([
  // First, sort by the desired fields
  { $sort: { score: -1, age: 1 } }, // Sort by score descending and age ascending

  // Push all documents into an array and add rankings
  {
    $group: {
      _id: null,  // Treat the entire collection as a single group
      users: { $push: "$ROOT" }  // Collect all documents into the users array
    }
  },
  {
    $unwind: {
      path: "$users",
      includeArrayIndex: "rank"  // includeArrayIndex assigns an index as the rank
    }
  },
  {
    $replaceRoot: {
      newRoot: {
        $mergeObjects: ["$users", { rank: { $add: ["$rank", 1] } }]  // Convert index to 1-based rank
      }
    }
  }
]).exec((err, results) => {
  if (err) throw err;
  console.log(results);  // Print the user list with rankings
});

Explanation:

1. Sorting: Apply $sort to order users with score descending and age ascending.
2. Grouping: Employ $group to collect all documents into an array, effectively treating the entire collection as a single group.
3. Unwinding: Apply $unwind to expand the array, using includeArrayIndex to assign an index as the rank.
4. Reconstructing Documents: Utilize $replaceRoot and $mergeObjects to combine the original documents with the added rank field, ensuring the rank starts from 1.

This aggregation pipeline both sorts the data and efficiently adds a rank field to each record, indicating its position in the sorted list. It is highly valuable for implementing features such as leaderboards.