$rank must be specified with a top level sortBy expression with exactly one element

[Nimisha]

I am getting an issue when trying to rank documents based on score and attempts:

 "$rank must be specified with a top level sortBy expression with exactly one element" 

The query is

user_score.aggregate([
{"$setWindowFields":{"sortBy":{"score":-1, "attempts":-1},
"output":{"scoreRank":{"$rank":{}}}}}])

Is it not possible to rank docs based on multiple fields?

[Jason_Tran]

Hi @Nimisha - Welcome to the community.

Is it not possible to rank docs based on multiple fields?

Yes, currently using $rank with multiple fields is not possible. There is currently a SERVER ticket raised for this behaviour - SERVER-56572. As noted on the ticket, a potential workaround is:

to prepend an $addFields stage that places the compound set of fields into their own sub-object, then applying the window function rank on that new object.

I am not sure if this would work for your use case but I had created the following documents in my test environment:

DB>db.user_score.find()
[
  { _id: ObjectId("638433ca31f9fbfd0869f475"), score: 1, attempts: 1 },
  { _id: ObjectId("638433ce31f9fbfd0869f476"), score: 1, attempts: 2 },
  { _id: ObjectId("638433d031f9fbfd0869f477"), score: 1, attempts: 3 },
  { _id: ObjectId("638433d731f9fbfd0869f478"), score: 2, attempts: 1 },
  { _id: ObjectId("638433d931f9fbfd0869f479"), score: 2, attempts: 2 },
  { _id: ObjectId("638433da31f9fbfd0869f47a"), score: 2, attempts: 3 },
  { _id: ObjectId("638433de31f9fbfd0869f47b"), score: 3, attempts: 3 },
  { _id: ObjectId("638433df31f9fbfd0869f47c"), score: 3, attempts: 1 },
  { _id: ObjectId("638433e131f9fbfd0869f47d"), score: 3, attempts: 2 },
  { _id: ObjectId("638433e231f9fbfd0869f47e"), score: 3, attempts: 3 }
]

I then created added an extra field with the "score" and "attempts" values within it as an object before running the $setWindowFields with $rank:

db.user_score.aggregate([
  {
    '$addFields': { testField: { score: '$score', attempts: '$attempts' } }
  },
  {
    '$setWindowFields': {
      sortBy: { testField: -1 },
      output: { scoreRank: { '$rank': {} } }
    }
  }
])
[
  {
    _id: ObjectId("638433de31f9fbfd0869f47b"),
    score: 3,
    attempts: 3,
    testField: { score: 3, attempts: 3 },
    scoreRank: 1
  },
  {
    _id: ObjectId("638433e231f9fbfd0869f47e"),
    score: 3,
    attempts: 3,
    testField: { score: 3, attempts: 3 },
    scoreRank: 1
  },
  {
    _id: ObjectId("638433e131f9fbfd0869f47d"),
    score: 3,
    attempts: 2,
    testField: { score: 3, attempts: 2 },
    scoreRank: 3
  },
  {
    _id: ObjectId("638433df31f9fbfd0869f47c"),
    score: 3,
    attempts: 1,
    testField: { score: 3, attempts: 1 },
    scoreRank: 4
  },
  {
    _id: ObjectId("638433da31f9fbfd0869f47a"),
    score: 2,
    attempts: 3,
    testField: { score: 2, attempts: 3 },
    scoreRank: 5
  },
  {
    _id: ObjectId("638433d931f9fbfd0869f479"),
    score: 2,
    attempts: 2,
    testField: { score: 2, attempts: 2 },
    scoreRank: 6
  },
  {
    _id: ObjectId("638433d731f9fbfd0869f478"),
    score: 2,
    attempts: 1,
    testField: { score: 2, attempts: 1 },
    scoreRank: 7
  },
  {
    _id: ObjectId("638433d031f9fbfd0869f477"),
    score: 1,
    attempts: 3,
    testField: { score: 1, attempts: 3 },
    scoreRank: 8
  },
  {
    _id: ObjectId("638433ce31f9fbfd0869f476"),
    score: 1,
    attempts: 2,
    testField: { score: 1, attempts: 2 },
    scoreRank: 9
  },
  {
    _id: ObjectId("638433ca31f9fbfd0869f475"),
    score: 1,
    attempts: 1,
    testField: { score: 1, attempts: 1 },
    scoreRank: 10
  }
]

In the meantime, I have also created a request to have the documentation updated to perhaps note that only a single element in the sortBy when using $rank.

Regards,
Jason