$bottom (aggregation accumulator)

Null and Missing Values

Consider the following aggregation that returns the bottom document from a group of scores:

• $bottom does not filter out null values.

• $bottom 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:
            {
               $bottom:
                  {
                     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 bottom element. PlayerD is returned as the bottom 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

$bottom is not supported as a aggregation expression.

$bottom is supported as a window operator.

Memory Limit Considerations

Aggregation pipelines which call $bottom are subject to the 100 MB limit. If this limit is exceeded for an individual group, the aggregation fails with an error.

Find the Bottom Score

You can use the $bottom accumulator to find the bottom score in a single game.

db.gamescores.aggregate( [
   {
      $match : { gameId : "G1" }
   },
   {
      $group:
         {
            _id: "$gameId",
            playerId:
               {
                  $bottom:
                  {
                     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 $bottom with output : ["$playerId"," $score"].

• Uses sortBy: { "score": -1 } to sort the scores in descending order.

• Uses $bottom to return the bottom score for the game.

The operation returns the following results:

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

Finding the Bottom Score Across Multiple Games

You can use the $bottom accumulator to find the bottom score in each game.

db.gamescores.aggregate( [
      {
         $group:
         { _id: "$gameId", playerId:
            {
               $bottom:
                  {
                     output: [ "$playerId", "$score" ],
                     sortBy: { "score": -1 }
                  }
            }
         }
      }
] )

The example pipeline:

• Uses $group to group the results by gameId.

• Uses $bottom to return the bottom score for each game.

• Specifies the fields that are output for $bottom with output : ["$playerId", "$score"].

• Uses sortBy: { "score": -1 } to sort the scores in descending order.

The operation returns the following results:

[
   { _id: 'G2', playerId: [ 'PlayerA', 10 ] },
   { _id: 'G1', playerId: [ 'PlayerD', 1 ] }
]