$rank (aggregation)
On this page
Definition
New in version 5.0.
Returns the document position (known as the rank) relative to other
documents in the $setWindowFields
stage partition.
The sortBy field value in the
$setWindowFields
stage determines the document rank. When
used with the $rank
operator, sortBy
can only take one field as
its value. For more information on how MongoDB compares fields with
different types, see BSON comparison order.
If multiple documents occupy the same rank, $rank
places
the document with the subsequent value at a rank with a gap
(see Behavior).
$rank
is only available in the $setWindowFields
stage.
$rank
syntax:
{ $rank: { } }
$rank
does not accept any parameters.
Behavior
$rank
and$denseRank
differ in how they rank duplicate sortBy field values. For example, with sortBy field values of 7, 9, 9, and 10:$denseRank
ranks the values as 1, 2, 2, and 3. The duplicate 9 values have a rank of 2, and 10 has a rank of 3. There is no gap in the ranks.$rank
ranks the values as 1, 2, 2, and 4. The duplicate 9 values have a rank of 2, and 10 has a rank of 4. There is a gap in the ranks for 3.
Documents with a
null
value for a sortBy field or documents missing the sortBy field are assigned a rank based on the BSON comparison order. See the example in Rank Partitions Containing Duplicate Values, Nulls, or Missing Data.Starting in MongoDB 8.0,
null
and missing field values in$denseRank
and$rank
sortBy operations are treated the same when calculating rankings. This change makes the behavior ofdenseRank
andrank
consistent with$sort
.
Examples
Create a cakeSales
collection that contains cake sales in the states
of California (CA
) and Washington (WA
):
db.cakeSales.insertMany( [ { _id: 0, type: "chocolate", orderDate: new Date("2020-05-18T14:10:30Z"), state: "CA", price: 13, quantity: 120 }, { _id: 1, type: "chocolate", orderDate: new Date("2021-03-20T11:30:05Z"), state: "WA", price: 14, quantity: 140 }, { _id: 2, type: "vanilla", orderDate: new Date("2021-01-11T06:31:15Z"), state: "CA", price: 12, quantity: 145 }, { _id: 3, type: "vanilla", orderDate: new Date("2020-02-08T13:13:23Z"), state: "WA", price: 13, quantity: 104 }, { _id: 4, type: "strawberry", orderDate: new Date("2019-05-18T16:09:01Z"), state: "CA", price: 41, quantity: 162 }, { _id: 5, type: "strawberry", orderDate: new Date("2019-01-08T06:12:03Z"), state: "WA", price: 43, quantity: 134 } ] )
Rank Partitions by an Integer Field
This example uses $rank
in the $setWindowFields
stage to output the quantity
rank of the cake sales for each
state
:
db.cakeSales.aggregate( [ { $setWindowFields: { partitionBy: "$state", sortBy: { quantity: -1 }, output: { rankQuantityForState: { $rank: {} } } } } ] )
In the example:
partitionBy: "$state"
partitions the documents in the collection bystate
. There are partitions forCA
andWA
.sortBy: { quantity: -1 }
sorts the documents in each partition byquantity
in descending order (-1
), so the highestquantity
is first.
output
sets therankQuantityForState
field to thequantity
rank using$rank
, as shown in the following results.
{ "_id" : 4, "type" : "strawberry", "orderDate" : ISODate("2019-05-18T16:09:01Z"), "state" : "CA", "price" : 41, "quantity" : 162, "rankQuantityForState" : 1 } { "_id" : 2, "type" : "vanilla", "orderDate" : ISODate("2021-01-11T06:31:15Z"), "state" : "CA", "price" : 12, "quantity" : 145, "rankQuantityForState" : 2 } { "_id" : 0, "type" : "chocolate", "orderDate" : ISODate("2020-05-18T14:10:30Z"), "state" : "CA", "price" : 13, "quantity" : 120, "rankQuantityForState" : 3 } { "_id" : 1, "type" : "chocolate", "orderDate" : ISODate("2021-03-20T11:30:05Z"), "state" : "WA", "price" : 14, "quantity" : 140, "rankQuantityForState" : 1 } { "_id" : 5, "type" : "strawberry", "orderDate" : ISODate("2019-01-08T06:12:03Z"), "state" : "WA", "price" : 43, "quantity" : 134, "rankQuantityForState" : 2 } { "_id" : 3, "type" : "vanilla", "orderDate" : ISODate("2020-02-08T13:13:23Z"), "state" : "WA", "price" : 13, "quantity" : 104, "rankQuantityForState" : 3 }
Rank Partitions by a Date Field
This example shows how to use dates with $rank
in the
$setWindowFields
stage to output the orderDate
rank of
the cake sales for each state
:
db.cakeSales.aggregate( [ { $setWindowFields: { partitionBy: "$state", sortBy: { orderDate: 1 }, output: { rankOrderDateForState: { $rank: {} } } } } ] )
In the example:
partitionBy: "$state"
partitions the documents in the collection bystate
. There are partitions forCA
andWA
.sortBy: { orderDate: 1 }
sorts the documents in each partition byorderDate
in ascending order (1
), so the earliestorderDate
is first.
output
sets therankOrderDateForState
field to theorderDate
rank using$rank
, as shown in the following results.
{ "_id" : 4, "type" : "strawberry", "orderDate" : ISODate("2019-05-18T16:09:01Z"), "state" : "CA", "price" : 41, "quantity" : 162, "rankOrderDateForState" : 1 } { "_id" : 0, "type" : "chocolate", "orderDate" : ISODate("2020-05-18T14:10:30Z"), "state" : "CA", "price" : 13, "quantity" : 120, "rankOrderDateForState" : 2 } { "_id" : 2, "type" : "vanilla", "orderDate" : ISODate("2021-01-11T06:31:15Z"), "state" : "CA", "price" : 12, "quantity" : 145, "rankOrderDateForState" : 3 } { "_id" : 5, "type" : "strawberry", "orderDate" : ISODate("2019-01-08T06:12:03Z"), "state" : "WA", "price" : 43, "quantity" : 134, "rankOrderDateForState" : 1 } { "_id" : 3, "type" : "vanilla", "orderDate" : ISODate("2020-02-08T13:13:23Z"), "state" : "WA", "price" : 13, "quantity" : 104, "rankOrderDateForState" : 2 } { "_id" : 1, "type" : "chocolate", "orderDate" : ISODate("2021-03-20T11:30:05Z"), "state" : "WA", "price" : 14, "quantity" : 140, "rankOrderDateForState" : 3 }
Rank Partitions Containing Duplicate Values, Nulls, or Missing Data
Create a cakeSalesWithDuplicates
collection where:
Cake sales are placed in the state of California (
CA
) and Washington (WA
).Documents 6 to 8 have the same
quantity
andstate
as document 5.Document 9 has the same
quantity
andstate
as document 4.Document 10 has a
null
quantity
.Document 11 is missing the
quantity
.
db.cakeSalesWithDuplicates.insertMany( [ { _id: 0, type: "chocolate", orderDate: new Date("2020-05-18T14:10:30Z"), state: "CA", price: 13, quantity: 120 }, { _id: 1, type: "chocolate", orderDate: new Date("2021-03-20T11:30:05Z"), state: "WA", price: 14, quantity: 140 }, { _id: 2, type: "vanilla", orderDate: new Date("2021-01-11T06:31:15Z"), state: "CA", price: 12, quantity: 145 }, { _id: 3, type: "vanilla", orderDate: new Date("2020-02-08T13:13:23Z"), state: "WA", price: 13, quantity: 104 }, { _id: 4, type: "strawberry", orderDate: new Date("2019-05-18T16:09:01Z"), state: "CA", price: 41, quantity: 162 }, { _id: 5, type: "strawberry", orderDate: new Date("2019-01-08T06:12:03Z"), state: "WA", price: 43, quantity: 134 }, { _id: 6, type: "strawberry", orderDate: new Date("2020-01-08T06:12:03Z"), state: "WA", price: 41, quantity: 134 }, { _id: 7, type: "strawberry", orderDate: new Date("2020-01-01T06:12:03Z"), state: "WA", price: 34, quantity: 134 }, { _id: 8, type: "strawberry", orderDate: new Date("2020-01-02T06:12:03Z"), state: "WA", price: 40, quantity: 134 }, { _id: 9, type: "strawberry", orderDate: new Date("2020-05-11T16:09:01Z"), state: "CA", price: 39, quantity: 162 }, { _id: 10, type: "strawberry", orderDate: new Date("2020-05-11T16:09:01Z"), state: "CA", price: 39, quantity: null }, { _id: 11, type: "strawberry", orderDate: new Date("2020-05-11T16:09:01Z"), state: "CA", price: 39 } ] )
This example uses $rank
in the $setWindowFields
stage to output the quantity
rank from the
cakeSalesWithDuplicates
collection for each state
:
db.cakeSalesWithDuplicates.aggregate( [ { $setWindowFields: { partitionBy: "$state", sortBy: { quantity: -1 }, output: { rankQuantityForState: { $rank: {} } } } } ] )
In the example:
partitionBy: "$state"
partitions the documents in the collection bystate
. There are partitions forCA
andWA
.sortBy: { quantity: -1 }
sorts the documents in each partition byquantity
in descending order (-1
), so the highestquantity
is first.
output
sets therankOrderDateForState
field to thequantity
rank using$rank
.
In the following example output:
Documents with the same
quantity
andstate
have the same rank. If documents have the same rank, there is a gap between that rank and the next rank.The document with the
null
quantity
and then the document with the missingquantity
are ranked the lowest in the output for theCA
partition. This sorting is the result of the BSON comparison order, which sortsnull
and missing values after number values in this example.
{ "_id" : 4, "type" : "strawberry", "orderDate" : ISODate("2019-05-18T16:09:01Z"), "state" : "CA", "price" : 41, "quantity" : 162, "rankQuantityForState" : 1 } { "_id" : 9, "type" : "strawberry", "orderDate" : ISODate("2020-05-11T16:09:01Z"), "state" : "CA", "price" : 39, "quantity" : 162, "rankQuantityForState" : 1 } { "_id" : 2, "type" : "vanilla", "orderDate" : ISODate("2021-01-11T06:31:15Z"), "state" : "CA", "price" : 12, "quantity" : 145, "rankQuantityForState" : 3 } { "_id" : 0, "type" : "chocolate", "orderDate" : ISODate("2020-05-18T14:10:30Z"), "state" : "CA", "price" : 13, "quantity" : 120, "rankQuantityForState" : 4 } { "_id" : 10, "type" : "strawberry", "orderDate" : ISODate("2020-05-11T16:09:01Z"), "state" : "CA", "price" : 39, "quantity" : null, "rankQuantityForState" : 5 } { "_id" : 11, "type" : "strawberry", "orderDate" : ISODate("2020-05-11T16:09:01Z"), "state" : "CA", "price" : 39, "rankQuantityForState" : 5 } { "_id" : 1, "type" : "chocolate", "orderDate" : ISODate("2021-03-20T11:30:05Z"), "state" : "WA", "price" : 14, "quantity" : 140, "rankQuantityForState" : 1 } { "_id" : 5, "type" : "strawberry", "orderDate" : ISODate("2019-01-08T06:12:03Z"), "state" : "WA", "price" : 43, "quantity" : 134, "rankQuantityForState" : 2 } { "_id" : 6, "type" : "strawberry", "orderDate" : ISODate("2020-01-08T06:12:03Z"), "state" : "WA", "price" : 41, "quantity" : 134, "rankQuantityForState" : 2 } { "_id" : 7, "type" : "strawberry", "orderDate" : ISODate("2020-01-01T06:12:03Z"), "state" : "WA", "price" : 34, "quantity" : 134, "rankQuantityForState" : 2 } { "_id" : 8, "type" : "strawberry", "orderDate" : ISODate("2020-01-02T06:12:03Z"), "state" : "WA", "price" : 40, "quantity" : 134, "rankQuantityForState" : 2 } { "_id" : 3, "type" : "vanilla", "orderDate" : ISODate("2020-02-08T13:13:23Z"), "state" : "WA", "price" : 13, "quantity" : 104, "rankQuantityForState" : 6 }