/ /

$topN (window function)

Definition

$topN

Returns the top n elements within a particular window, according to the specified sort order.

Note

Other Uses of $topN

This page describes $topN when used as a window function.

You can also use $topN in these other contexts:

$topN (accumulator), which returns the top n elements within a group according to the specified sort order.
$topN (expression), which returns the top n elements from an array.

Syntax

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

Options

Field	Necessity	Description
`n`	Required	Number of results to return. Must resolve to a positive integral value. If the window contains fewer than `n` elements, `$topN` returns all elements in the window.
`output`	Required	Determines the output for each document in the window. Can be any expression.
`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.

Behavior

Null and Missing Values

$topN does not filter out null values.
$topN 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 Three Highest Rated Movies in a Window

This example uses $topN in $setWindowFields to return each movie with the three highest-rated movies released in the same year.

db.movies.aggregate( [
   {
      $match: {
         year: { $in: [ 1999, 2001 ] },
         "imdb.rating": { $gte: 8.5 }
      }
   },
   {
      $setWindowFields: {
         partitionBy: "$year",
         output: {
            topThreeRatedInYear: {
               $topN: {
                  sortBy: { "imdb.rating": -1, title: 1 },
                  output: { rating: "$imdb.rating", title: "$title" },
                  n: 3
               },
               window: {
                  documents: [ "unbounded", "unbounded" ]
               }
            }
         }
      }
   },
   {
      $project: {
         _id: 0,
         title: 1,
         year: 1,
         "imdb.rating": 1,
         topThreeRatedInYear: 1
      }
   },
   {
      $sort: { year: 1, "imdb.rating": -1 }
   }
] )

[
  {
    imdb: { rating: 8.9 },
    year: 1999,
    title: 'Fight Club',
    topThreeRatedInYear: [
      { rating: 8.9, title: 'Fight Club' },
      { rating: 8.7, title: 'Eddie Izzard: Dress to Kill' },
      { rating: 8.7, title: 'The Matrix' }
    ]
  },
  {
    imdb: { rating: 8.7 },
    year: 1999,
    title: 'The Matrix',
    topThreeRatedInYear: [
      { rating: 8.9, title: 'Fight Club' },
      { rating: 8.7, title: 'Eddie Izzard: Dress to Kill' },
      { rating: 8.7, title: 'The Matrix' }
    ]
  },
  {
    title: 'Eddie Izzard: Dress to Kill',
    year: 1999,
    imdb: { rating: 8.7 },
    topThreeRatedInYear: [
      { rating: 8.9, title: 'Fight Club' },
      { rating: 8.7, title: 'Eddie Izzard: Dress to Kill' },
      { rating: 8.7, title: 'The Matrix' }
    ]
  },
  {
    imdb: { rating: 8.5 },
    year: 1999,
    title: 'The Green Mile',
    topThreeRatedInYear: [
      { rating: 8.9, title: 'Fight Club' },
      { rating: 8.7, title: 'Eddie Izzard: Dress to Kill' },
      { rating: 8.7, title: 'The Matrix' }
    ]
  },
  {
    title: 'Band of Brothers',
    year: 2001,
    imdb: { rating: 9.6 },
    topThreeRatedInYear: [
      { rating: 9.6, title: 'Band of Brothers' },
      { rating: 9.2, title: 'The Blue Planet' },
      {
        rating: 8.8,
        title: 'The Lord of the Rings: The Fellowship of the Ring'
      }
    ]
  },
  {
    title: 'The Blue Planet',
    year: 2001,
    imdb: { rating: 9.2 },
    topThreeRatedInYear: [
      { rating: 9.6, title: 'Band of Brothers' },
      { rating: 9.2, title: 'The Blue Planet' },
      {
        rating: 8.8,
        title: 'The Lord of the Rings: The Fellowship of the Ring'
      }
    ]
  },
  {
    imdb: { rating: 8.8 },
    year: 2001,
    title: 'The Lord of the Rings: The Fellowship of the Ring',
    topThreeRatedInYear: [
      { rating: 9.6, title: 'Band of Brothers' },
      { rating: 9.2, title: 'The Blue Planet' },
      {
        rating: 8.8,
        title: 'The Lord of the Rings: The Fellowship of the Ring'
      }
    ]
  },
  {
    imdb: { rating: 8.6 },
    year: 2001,
    title: 'Spirited Away',
    topThreeRatedInYear: [
      { rating: 9.6, title: 'Band of Brothers' },
      { rating: 9.2, title: 'The Blue Planet' },
      {
        rating: 8.8,
        title: 'The Lord of the Rings: The Fellowship of the Ring'
      }
    ]
  },
  {
    title: 'Promises',
    year: 2001,
    imdb: { rating: 8.5 },
    topThreeRatedInYear: [
      { rating: 9.6, title: 'Band of Brothers' },
      { rating: 9.2, title: 'The Blue Planet' },
      {
        rating: 8.8,
        title: 'The Lord of the Rings: The Fellowship of the Ring'
      }
    ]
  }
]

In the preceding example:

$match filters for movies released in 1999 or 2001 with an IMDb rating of at least 8.5.
$setWindowFields partitions the documents by year.
window: { documents: [ "unbounded", "unbounded" ] } spans the entire year partition, so $topN considers all documents in the year when determining the three highest-rated movies.
$topN uses sortBy: { "imdb.rating": -1, title: 1 } to rank movies by IMDb score and uses title as a tie-breaker. Every document in the same year receives the same topThreeRatedInYear value.
$project removes unneeded fields to highlight the window function result.
$sort sorts the output by year ascending and rating descending.

Back

$top

Server Sessions