/ /

$bottom (window function)

Definition

$bottom: New in version 5.2.

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

Note

Disambiguation

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

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

Syntax

{
   $setWindowFields: {
      ...
      output: {
         <field>: {
            $bottom: {
               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

Null and Missing Values

$bottom does not filter out null values.
$bottom 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.

Compute Relative Rating to Lowest-Rated Movie Over a Window

This example uses $bottom as a window function to compute the rating of the current document relative to the lowest-rated movie in the same year. Each movie document contains that movie's rating and a calculated field showing how much higher the movie's rating is compared to the lowest-rated movie from the same year.

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

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

In this example:

The $match stage filters for PG-rated Family movies from 1980-1982.
$setWindowFields partitions the documents by year.
The window spans from unbounded to unbounded, meaning $bottom considers all documents in the partition when determining the lowest-rated movie.
$bottom returns the lowest-rated movie in each partition as an embedded document containing the title and rating fields.
$project computes ratingAboveWorst by subtracting the lowest rating from each movie's rating, showing how much each movie is rated above the worst performer in its year.
$limit limits the output to 10 documents.

This approach preserves all individual movie documents and includes a comparison for each to the worst performer in its year.

Back

$avg

$bottomN