Add field to documents after $sort aggregation pipeline which include its index in sorted list using MongoDb aggregation

Question

I want to get the order of some user from a list after $sort aggregation pipeline.

Let's say we have a leaderboard, and I need to get my rank in the leaderboard with only one query getting only my data.

I have tried $addFields and some queries with $map

Let's say we have these documents

/* 1 createdAt:8/18/2019, 4:42:41 PM*/
{
    "_id" : ObjectId("5d5963e1c6c93b2da849f067"),
    "name" : "x4",
    "points" : 69
},

/* 2 createdAt:8/18/2019, 4:42:41 PM*/
{
    "_id" : ObjectId("5d5963e1c6c93b2da849f07b"),
    "name" : "x24",
    "points" : 968
},

/* 3 createdAt:8/18/2019, 4:42:41 PM*/
{
    "_id" : ObjectId("5d5963e1c6c93b2da849f06a"),
    "name" : "x7",
    "points" : 997
},

And I want to write a query like this

db.table.aggregate(
   [
     { $sort : { points : 1 } },
     { $addFields: { order : "$index" } },
     { $match : { name : "x24" } }
   ]
)

I need to inject the order field with something like $index

I expect to have something like this in return

{
   "_id" : ObjectId("5d5963e1c6c93b2da849f07b"),
   "name" : "x24",
   "points" : 968,
   "order" : 2
}

I need something like the metadata of the result here which return 2

/* 2 createdAt:8/18/2019, 4:42:41 PM*/

Answer 1

One of the workaround for this situation is to convert your all documents into one single array and hence resolve the index of the document using this array with help of $unwind and finally project the data with fields as required.

db.collection.aggregate([
  { $sort: { points: 1 } },
  {
    $group: {
      _id: 1,
      register: { $push: { _id: "$_id", name: "$name", points: "$points" } }
    }
  },
  { $unwind: { path: "$register", includeArrayIndex: "order" } },
  { $match: { "register.name": "x4" } },
  {
    $project: {
      _id: "$register._id",
      name: "$register.name",
      points: "$register.points",
      order: 1
    }
  }
]);

To make it more efficient you can apply limit, match, and filter as per your requirement.