$top (accumulator operator)

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

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

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.

BSON Data Type Sort Ordering

When sorting different types, the order of BSON data types is used to determine ordering. As an example, consider a collection whose values consist of strings and numbers.

• In an ascending sort, string values are sorted after numeric values.

• In a descending sort, string values are sorted before numeric values.

In this example:

• PlayerA has an integer score.

• PlayerB has a string "2" score.

• PlayerC has an empty string score.

Because the sort is in descending { "score": -1 }, string values are sorted before PlayerA's numeric score. $top returns the first element after sorting, which is PlayerB:

db.aggregate( [
   {
      $documents: [
         { playerId: "PlayerA", gameId: "G1", score: 1 },
         { playerId: "PlayerB", gameId: "G1", score: "2" },
         { playerId: "PlayerC", gameId: "G1", score: "" }
      ]
   },
   {
      $group:
         {
            _id: "$gameId",
            playerId:
               {
                  $top:
                  {
                     output: [ "$playerId", "$score" ],
                     sortBy: { "score": -1 }
                  }
               }
         }
   }
] )

[ { _id: 'G1', playerId: [ 'PlayerB', '2' ] } ]

Find the Top Rating in a Genre

You can use the $top accumulator to find the highest-rated movie in a genre.

db.movies.aggregate( [
   {
      $match: {
         genres: "Comedy",
         "imdb.rating": { $gt: 0 }
      }
   },
   {
      $group:
         {
            _id: "Comedy",
            highestRatedMovie:
               {
                  $top:
                  {
                     output: [ "$title", "$imdb.rating" ],
                     sortBy: { "imdb.rating": -1 }
                  }
               }
         }
   }
] )

[
  { _id: 'Comedy', highestRatedMovie: [ 'Over the Garden Wall', 9.2 ] }
]

The example pipeline:

• Uses $match to filter for Comedy movies with a positive IMDb rating.

• Uses $group to group all Comedy movies under a single "Comedy" group key.

• Specifies the fields that are output for $top with output : ["$title", "$imdb.rating"].

• Uses sortBy: { "imdb.rating": -1 } to rank movies within the group by IMDb rating in descending order, which determines the movie that $top returns.

Find the Top Rating Across Multiple Ratings

You can use the $top accumulator to find the highest-rated movie for each movie rating category.

db.movies.aggregate( [
   {
      $match: {
         rated: { $in: [ "G", "PG", "PG-13", "R" ] },
         "imdb.rating": { $gt: 0 }
      }
   },
   {
      $group:
      {
         _id: "$rated",
         highestRatedMovie:
            {
               $top:
                  {
                     output: [ "$title", "$imdb.rating" ],
                     sortBy: { "imdb.rating": -1 }
                  }
            }
      }
   },
   {
      $sort: { _id: 1 }
   }
] )

[
  { _id: 'G', highestRatedMovie: [ 'Modern Times', 8.6 ] },
  { _id: 'PG', highestRatedMovie: [ 'Life', 9.2 ] },
  {
    _id: 'PG-13',
    highestRatedMovie: [ 'A Brave Heart: The Lizzie Velasquez Story', 9.4 ]
  },
  { _id: 'R', highestRatedMovie: [ 'The Shawshank Redemption', 9.3 ] }
]

The example pipeline:

• Uses $match to filter for movies in the G, PG, PG-13, and R categories with a positive IMDb rating.

• Uses $group to group the results by rated.

• Uses $top to return the highest-rated movie for each category.

• Specifies the fields that are output for $top with output : ["$title", "$imdb.rating"].

• Uses sortBy: { "imdb.rating": -1 } to rank movies within each group by IMDb rating in descending order, which determines the movie that $top returns.

• Uses $sort to sort the results alphabetically by rating category.