Earlier today someone asked me if it was possible to do dense ranking using aggregation framework. If you need a reminder of rank vs dense rank (I did) rank is the one that ranks sequentially making ties have the same rank but then skipping the rank that would have been used if there was no tie. So if the values we have are [ 100, 96, 96, 25, 25, 1 ] then ranks would be [ 1, 2, 2, 4, 4, 6]. Dense rank will also give ties the same rank, but it doesn't skip any "position" so the dense ranks for the same set would be: [ 1, 2, 2, 3, 3, 4 ].
Since ranking is done within specific "grouping", you're probably not surprised that $group stage is going to be involved. To get the scores (whatever you want to rank by) in order you can use $sort on that field first (assuming you have an index to support it) or you can $group with $push first and then sort the array in each document. Then you need to do ranking. Because it's a pretty complex expression, I created a helper function that generates it based on appropriate inputs:
Since ranking is done within specific "grouping", you're probably not surprised that $group stage is going to be involved. To get the scores (whatever you want to rank by) in order you can use $sort on that field first (assuming you have an index to support it) or you can $group with $push first and then sort the array in each document. Then you need to do ranking. Because it's a pretty complex expression, I created a helper function that generates it based on appropriate inputs:
That's it! Now that I have that function, I can pass in my array of objects, specifying which field is being used for ranking, and whether or not I want dense ranking or regular ranking.
The most sharp-eyed of you may have noticed that I must be running this in the latest 3.5 development release, because I used the "$mergeObjects" expression in my rankArray function. You can simulate the functionality of "$mergeObjects" by using $objectToArray, $concatArrays and $arrayToObject to do exactly the same thing in 3.4.4 or later, or if you are on an earlier version, instead of $mergeObjects of "$$this" and {"rank": "$$rank"} you can write the new object yourself explicitly:
{
"emp": "$$this.emp",
"sal" : "$$this.sal",
"rank": "$$rank"
}
$mergeObjects is only one of many great enhancements coming to MongoDB 3.6.
{
"emp": "$$this.emp",
"sal" : "$$this.sal",
"rank": "$$rank"
}
$mergeObjects is only one of many great enhancements coming to MongoDB 3.6.
RSS Feed
