/ /

$bottomN (window function)

Definition

$bottomN

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

Note

Disambiguation

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

You can also use $bottomN (accumulator), which returns the bottom n elements within a group according to the specified sort order.

Syntax

{
   $setWindowFields: {
      ...
      output: {
         <field>: {
            $bottomN: {
               n: <expression>,
               output: <expression>,
               sortBy: { <field1>: <sort order>, <field2>: <sort order> ... }
            },
            window: {
               documents: [ <lower boundary>, <upper boundary> ]
            }
         }
      }
   }
}

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, `$bottomN` 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.

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 Lowest Rated Movies in a Window

This example uses the $bottomN window function to return each movie along with the three lowest-rated movies released in the same year.

db.movies.aggregate( [
   {
      $unwind: "$genres"
   },
   {
      $match: {
         genres: "Action",
         rated: "PG-13",
         "imdb.rating": { $lte: 5 },
         year: { $in: [ 2000, 2001, 2002 ] }
      }
   },
   {
      $setWindowFields: {
         partitionBy: "$year",
         output: {
            lowestThreeMovies: {
               $bottomN: {
                  sortBy: { "imdb.rating": -1, title: 1 },
                  output: [ "$title", "$imdb.rating" ],
                  n: 3
               },
               window: {
                  documents: [ "unbounded", "unbounded" ]
               }
            }
         }
      }
   },
   {
      $project: {
         _id: 0,
         title: 1,
         year: 1,
         "imdb.rating": 1,
         lowestThreeMovies: 1
      }
   }
] )

[
  {
    title: 'Knockout',
    year: 2000,
    imdb: { rating: 3.8 },
    lowestThreeMovies: [ [ 'Knockout', 3.8 ], [ 'Dungeons & Dragons', 3.6 ], [ 'Battlefield Earth', 2.4 ] ]
  },
  {
    title: 'Battlefield Earth',
    year: 2000,
    imdb: { rating: 2.4 },
    lowestThreeMovies: [ [ 'Knockout', 3.8 ], [ 'Dungeons & Dragons', 3.6 ], [ 'Battlefield Earth', 2.4 ] ]
  },
  {
    title: 'Dungeons & Dragons',
    year: 2000,
    imdb: { rating: 3.6 },
    lowestThreeMovies: [ [ 'Knockout', 3.8 ], [ 'Dungeons & Dragons', 3.6 ], [ 'Battlefield Earth', 2.4 ] ]
  },
  {
    title: 'Left Behind: The Movie',
    year: 2000,
    imdb: { rating: 4.6 },
    lowestThreeMovies: [ [ 'Knockout', 3.8 ], [ 'Dungeons & Dragons', 3.6 ], [ 'Battlefield Earth', 2.4 ] ]
  },
  {
    title: "Big Momma's House",
    year: 2000,
    imdb: { rating: 5 },
    lowestThreeMovies: [ [ 'Knockout', 3.8 ], [ 'Dungeons & Dragons', 3.6 ], [ 'Battlefield Earth', 2.4 ] ]
  },
  {
    title: 'Driven',
    year: 2001,
    imdb: { rating: 4.5 },
    lowestThreeMovies: [ [ 'Driven', 4.5 ], [ 'Megiddo: The Omega Code 2', 4.1 ], [ 'Venomous', 3.9 ] ]
  },
  {
    title: 'Driven',
    year: 2001,
    imdb: { rating: 4.5 },
    lowestThreeMovies: [ [ 'Driven', 4.5 ], [ 'Megiddo: The Omega Code 2', 4.1 ], [ 'Venomous', 3.9 ] ]
  },
  {
    title: 'Megiddo: The Omega Code 2',
    year: 2001,
    imdb: { rating: 4.1 },
    lowestThreeMovies: [ [ 'Driven', 4.5 ], [ 'Megiddo: The Omega Code 2', 4.1 ], [ 'Venomous', 3.9 ] ]
  },
  {
    title: 'Venomous',
    year: 2001,
    imdb: { rating: 3.9 },
    lowestThreeMovies: [ [ 'Driven', 4.5 ], [ 'Megiddo: The Omega Code 2', 4.1 ], [ 'Venomous', 3.9 ] ]
  },
  {
    title: 'The Adventures of Pluto Nash',
    year: 2002,
    imdb: { rating: 3.7 },
    lowestThreeMovies: [ [ 'Half Past Dead', 4.5 ], [ 'Extreme Ops', 4.4 ], [ 'The Adventures of Pluto Nash', 3.7 ] ]
  },
  {
    title: 'Extreme Ops',
    year: 2002,
    imdb: { rating: 4.4 },
    lowestThreeMovies: [ [ 'Half Past Dead', 4.5 ], [ 'Extreme Ops', 4.4 ], [ 'The Adventures of Pluto Nash', 3.7 ] ]
  },
  {
    title: 'The Touch',
    year: 2002,
    imdb: { rating: 4.6 },
    lowestThreeMovies: [ [ 'Half Past Dead', 4.5 ], [ 'Extreme Ops', 4.4 ], [ 'The Adventures of Pluto Nash', 3.7 ] ]
  },
  {
    title: 'Half Past Dead',
    year: 2002,
    imdb: { rating: 4.5 },
    lowestThreeMovies: [ [ 'Half Past Dead', 4.5 ], [ 'Extreme Ops', 4.4 ], [ 'The Adventures of Pluto Nash', 3.7 ] ]
  }
]

In the preceding example:

$unwind deconstructs the genres array so each movie document only contains a single genre.
$match restricts the input to Action movies that are rated PG-13, have an imdb.rating value of 5 or less, and were released in 2000, 2001, or 2002.
$setWindowFields partitions the documents by year.
window: { documents: [ "unbounded", "unbounded" ] } spans the entire year partition, so $bottomN considers all documents in the year when determining the three lowest-rated movies.
$bottomN 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 lowestThreeMovies value.
$project removes unneeded fields to highlight the window function result.

Back

$bottom

$count