$top (aggregation accumulator)
On this page
Definition
Syntax
{ $top: { sortBy: { <field1>: <sort order>, <field2>: <sort order> ... }, output: <expression> } }
Field | Necessity | Description |
---|---|---|
sortBy | Required | Specifies the order of results, with syntax similar to
$sort . |
output | Required | Represents the output for each element in the group
and can be any expression. |
Behavior
Null and Missing Values
Consider the following aggregation that returns the top document from a group of scores:
$top
does not filter out null values.$top
converts missing values to null.
db.aggregate( [ { $documents: [ { playerId: "PlayerA", gameId: "G1", score: 1 }, { playerId: "PlayerB", gameId: "G1", score: 2 }, { playerId: "PlayerC", gameId: "G1", score: 3 }, { playerId: "PlayerD", gameId: "G1"}, { playerId: "PlayerE", gameId: "G1", score: null } ] }, { $group: { _id: "$gameId", playerId: { $top: { output: [ "$playerId", "$score" ], sortBy: { "score": 1 } } } } } ] )
In this example:
$documents
creates the literal documents that contain player scores.$group
groups the documents bygameId
. This example has only onegameId
,G1
.PlayerD
has a missing score andPlayerE
has a nullscore
. These values are both considered as null.The
playerId
andscore
fields are specified asoutput : ["$playerId"," $score"]
and returned as array values.Specify the sort order with
sortBy: { "score": 1 }
.PlayerD
andPlayerE
tied for the top element.PlayerD
is returned as the topscore
.To have more deterministic tie breaking behavior for multiple null values, add more fields to
sortBy
.
[ { _id: 'G1', playerId: [ 'PlayerD', null ] } ]
Restrictions
Window Function and Aggregation Expression Support
$top
is not supported as a
aggregation expression.
$top
is supported as a
window operator
.
Memory Limit Considerations
Aggregation pipelines which call $top
are subject to the
100 MB limit. If this
limit is exceeded for an individual group, the aggregation fails
with an error.
Examples
Consider a gamescores
collection with the following documents:
db.gamescores.insertMany([ { playerId: "PlayerA", gameId: "G1", score: 31 }, { playerId: "PlayerB", gameId: "G1", score: 33 }, { playerId: "PlayerC", gameId: "G1", score: 99 }, { playerId: "PlayerD", gameId: "G1", score: 1 }, { playerId: "PlayerA", gameId: "G2", score: 10 }, { playerId: "PlayerB", gameId: "G2", score: 14 }, { playerId: "PlayerC", gameId: "G2", score: 66 }, { playerId: "PlayerD", gameId: "G2", score: 80 } ])
Find the Top Score
You can use the $top
accumulator to find the top score in a
single game.
db.gamescores.aggregate( [ { $match : { gameId : "G1" } }, { $group: { _id: "$gameId", playerId: { $top: { output: [ "$playerId", "$score" ], sortBy: { "score": -1 } } } } } ] )
The example pipeline:
Uses
$match
to filter the results on a singlegameId
. In this case,G1
.Uses
$group
to group the results bygameId
. In this case,G1
.Specifies the fields that are output for
$top
withoutput : ["$playerId"," $score"]
.Uses
sortBy: { "score": -1 }
to sort the scores in descending order.Uses
$top
to return the top score in the game.
The operation returns the following results:
[ { _id: 'G1', playerId: [ 'PlayerC', 99 ] } ]
Find the Top Score
Across Multiple Games
You can use the $top
accumulator to find the top score
in each game.
db.gamescores.aggregate( [ { $group: { _id: "$gameId", playerId: { $top: { output: [ "$playerId", "$score" ], sortBy: { "score": -1 } } } } } ] )
The example pipeline:
Uses
$group
to group the results bygameId
.Uses
$top
to return topscore
for each game.Specifies the fields that are output for
$top
withoutput : ["$playerId", "$score"]
.Uses
sortBy: { "score": -1 }
to sort the scores in descending order.
The operation returns the following results:
[ { _id: 'G2', playerId: [ 'PlayerD', 80 ] }, { _id: 'G1', playerId: [ 'PlayerC', 99 ] } ]