Navigation

$top (aggregation accumulator)

On this page

Definition

$top

New in version 5.2.

Returns the top element within a group according to the specified sort order.

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.
copy 
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 ] ]
   }
]

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:

copy 
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.

copy 
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.

copy 
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 ] }
]

© MongoDB, Inc 2008-present. MongoDB, Mongo, and the leaf logo are registered trademarks of MongoDB, Inc.