Navigation

$bottom (aggregation accumulator)

On this page

Definition

$bottom

New in version 5.2.

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

Syntax

{
   $bottom:
      {
         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 bottom document from a group of scores:

  • $bottom does not filter out null values.
  • $bottom 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:
            {
               $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 ] ]
   }
]

Restrictions

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.

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 Bottom Score

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

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

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

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