$top (aggregation accumulator)

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 by gameId. This example has only one gameId, G1.

• PlayerD has a missing score and PlayerE has a null score. These values are both considered as null.

• The playerId and score fields are specified as output : ["$playerId"," $score"] and returned as array values.

• Specify the sort order with sortBy: { "score": 1 }.

• PlayerD and PlayerE tied for the top element. PlayerD is returned as the top score.

• To have more deterministic tie breaking behavior for multiple null values, add more fields to``sortBy``.

[
   {
      _id: 'G1',
      playerId: [ [ 'PlayerD', null ] ]
   }
]

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.

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 single gameId. In this case, G1.

• Uses $group to group the results by gameId. In this case, G1.

• Specifies the fields that are output for $top with output : ["$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 by gameId.

• Uses $top to return top score for each game.

• Specifies the fields that are output for $top with output : ["$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 ] }
]