/ /

$top (window function)

Definition

$top

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

Note

Disambiguation

This page describes $top when used as a window function. You can also use $top in these other contexts:

$top (expression operator), which returns the top element of an array based on a specified sort order.
$top (accumulator), which returns the top element within a group according to the specified sort order.

Syntax

{
   $setWindowFields: {
      ...
      output: {
         <field>: {
            $top: {
               sortBy: { <field1>: <sort order>, <field2>: <sort order> ... },
               output: <expression>
            }
         }
      }
   }
}

Field	Necessity	Description
sortBy	Required	Defines the ranking order of elements within the window, with syntax similar to `$sort`. Does not affect the order of documents in pipeline output.
output	Required	Specifies the output for each element in the window. Can be any expression.

Behavior

The $top window function has the following behaviors for null and missing values:

$top does not filter out null values.
$top converts missing values to null.

Example

The examples on this page use data from the sample_mflix dataset. For details on how to load this dataset into your self-managed MongoDB deployment, see Load the Sample Dataset. If you made any modifications to the sample databases, you may need to drop and recreate the databases to run the examples on this page.

Find the Highest-Rated Movie Per Year Over a Window

This example uses $top as a window function to identify the highest-rated movie in the same year for each movie document. The dataset is filtered to include only PG-rated Family movies from 1980–1982.

db.movies.aggregate( [
   {
      $match: {
         year: { $in: [ 1980, 1981, 1982 ] },
         "imdb.rating": { $exists: true, $ne: null },
         genres: "Family",
         rated: "PG"
      }
   },
   {
      $setWindowFields: {
         partitionBy: "$year",
         output: {
            highestRatedInYear: {
               $top: {
                  output: { title: "$title", rating: "$imdb.rating" },
                  sortBy: { "imdb.rating": -1 }
               },
               window: {
                  documents: [ "unbounded", "unbounded" ]
               }
            }
         }
      }
   },
   {
      $project: {
         _id: 0,
         title: 1,
         year: 1,
         rating: "$imdb.rating",
         highestRatedInYear: 1
      }
   },
   {
      $limit: 10
   }
] )

[
  {
    title: 'My Bodyguard',
    year: 1980,
    highestRatedInYear: { title: 'My Bodyguard', rating: 7 },
    rating: 7
  },
  {
    title: 'Popeye',
    year: 1980,
    highestRatedInYear: { title: 'My Bodyguard', rating: 7 },
    rating: 5.2
  },
  {
    title: 'The Watcher in the Woods',
    year: 1980,
    highestRatedInYear: { title: 'My Bodyguard', rating: 7 },
    rating: 6.5
  },
  {
    title: 'Clash of the Titans',
    year: 1981,
    highestRatedInYear: { title: 'Clash of the Titans', rating: 6.9 },
    rating: 6.9
  },
  {
    title: 'Going Ape!',
    year: 1981,
    highestRatedInYear: { title: 'Clash of the Titans', rating: 6.9 },
    rating: 3.6
  },
  {
    title: 'Night Crossing',
    year: 1982,
    highestRatedInYear: { title: 'E.T. the Extra-Terrestrial', rating: 7.9 },
    rating: 6.4
  },
  {
    year: 1982,
    title: 'Annie',
    highestRatedInYear: { title: 'E.T. the Extra-Terrestrial', rating: 7.9 },
    rating: 6.5
  },
  {
    title: 'Author! Author!',
    year: 1982,
    highestRatedInYear: { title: 'E.T. the Extra-Terrestrial', rating: 7.9 },
    rating: 6.1
  },
  {
    title: 'The Dark Crystal',
    year: 1982,
    highestRatedInYear: { title: 'E.T. the Extra-Terrestrial', rating: 7.9 },
    rating: 7.2
  },
  {
    year: 1982,
    title: 'E.T. the Extra-Terrestrial',
    highestRatedInYear: { title: 'E.T. the Extra-Terrestrial', rating: 7.9 },
    rating: 7.9
  }
]

In this example:

The $match stage filters for PG-rated Family movies from 1980-1982 to produce a small, focused result set.
$setWindowFields partitions the documents by year.
The window spans from unbounded to unbounded, meaning $top considers all documents in the partition when determining the highest-rated movie.
$top returns the highest-rated movie in each partition as an embedded document containing the title and rating fields.
$project includes only the title, year, rating, and highestRatedInYear fields in the output.
$limit limits the output to 10 documents.

This approach preserves all individual movie documents and includes a reference to the top performer in each year.

Back

$sum

$topN