/ /

$lastN (window function)

Definition

$lastN

Returns an array containing the last n elements within a window.

Note

Disambiguation

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

You can also use $lastN in these other contexts:

$lastN (accumulator), used in grouping to return the last n elements from each group.
$lastN (expression), which returns the specified number of elements from the end of an array.

Syntax

{
   $setWindowFields: {
      ...
      output: {
         <field>: {
            $lastN: {
               input: <expression>,
               n: <expression>
            }
         }
      }
   }
}

Options

Field	Type	Description
`input`	Expression	The `input` expression is evaluated over each document in the window, and the last `n` results are preserved.
`n`	Expression	The number of elements that `$lastN` returns from the window. Must resolve to a positive integer. If the window contains fewer than `n` elements, `$lastN` returns all elements in the window.

Behavior

$lastN has the following behaviors for null and missing values:

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

This example uses $setWindowFields to partition movies by year, sort them by imdb.rating in descending order, and return the three lowest-rated movies from each year.

db.movies.aggregate( [
   {
      $match: {
         year: { $in: [ 1979, 1985, 1992 ] },
         "imdb.rating": { $lte: 4.5, $gt: 0 }
      }
   },
   {
      $setWindowFields: {
         partitionBy: "$year",
         sortBy: { "imdb.rating": -1, "title": 1 },
         output: {
            lowestRatedThreeMoviesInYear: {
               $lastN: {
                  input: "$title",
                  n: 3
               },
               window: {
                  documents: [ "unbounded", "unbounded" ]
               }
            }
         }
      }
   },
   {
      $project: {
         _id: 0,
         title: 1,
         year: 1,
         "imdb.rating": 1,
         lowestRatedThreeMoviesInYear: 1
      }
   },
   {
      $sort: { year: 1, "imdb.rating": -1, "title": 1 }
   }
] )

[
  {
    title: 'Beyond the Poseidon Adventure',
    year: 1979,
    imdb: { rating: 4.4 },
    lowestRatedThreeMoviesInYear: [
      "The Concorde... Airport '79",
      'The Dark',
      'The Clonus Horror'
    ]
  },
  {
    title: "The Concorde... Airport '79",
    year: 1979,
    imdb: { rating: 4.2 },
    lowestRatedThreeMoviesInYear: [
      "The Concorde... Airport '79",
      'The Dark',
      'The Clonus Horror'
    ]
  },
  {
    title: 'The Dark',
    year: 1979,
    imdb: { rating: 4.1 },
    lowestRatedThreeMoviesInYear: [
      "The Concorde... Airport '79",
      'The Dark',
      'The Clonus Horror'
    ]
  },
  {
    title: 'The Clonus Horror',
    year: 1979,
    imdb: { rating: 3.4 },
    lowestRatedThreeMoviesInYear: [
      "The Concorde... Airport '79",
      'The Dark',
      'The Clonus Horror'
    ]
  },
  {
    title: 'Morons from Outer Space',
    year: 1985,
    imdb: { rating: 4.4 },
    lowestRatedThreeMoviesInYear: [ 'Creature', 'Perfect', 'Gymkata' ]
  },
  {
    title: "The Slugger's Wife",
    year: 1985,
    imdb: { rating: 4.3 },
    lowestRatedThreeMoviesInYear: [ 'Creature', 'Perfect', 'Gymkata' ]
  },
  {
    title: 'Creature',
    year: 1985,
    imdb: { rating: 4.2 },
    lowestRatedThreeMoviesInYear: [ 'Creature', 'Perfect', 'Gymkata' ]
  },
  {
    title: 'Perfect',
    year: 1985,
    imdb: { rating: 4.2 },
    lowestRatedThreeMoviesInYear: [ 'Creature', 'Perfect', 'Gymkata' ]
  },
  {
    title: 'Gymkata',
    year: 1985,
    imdb: { rating: 4.1 },
    lowestRatedThreeMoviesInYear: [ 'Creature', 'Perfect', 'Gymkata' ]
  },
  {
    title: 'Christopher Columbus: The Discovery',
    year: 1992,
    imdb: { rating: 4.4 },
    lowestRatedThreeMoviesInYear: [
      'Children of the Corn II: The Final Sacrifice',
      'Stop! Or My Mom Will Shoot',
      'Shadowchaser'
    ]
  },
  {
    title: 'Children of the Corn II: The Final Sacrifice',
    year: 1992,
    imdb: { rating: 4.1 },
    lowestRatedThreeMoviesInYear: [
      'Children of the Corn II: The Final Sacrifice',
      'Stop! Or My Mom Will Shoot',
      'Shadowchaser'
    ]
  },
  {
    title: 'Stop! Or My Mom Will Shoot',
    year: 1992,
    imdb: { rating: 4 },
    lowestRatedThreeMoviesInYear: [
      'Children of the Corn II: The Final Sacrifice',
      'Stop! Or My Mom Will Shoot',
      'Shadowchaser'
    ]
  },
  {
    title: 'Shadowchaser',
    year: 1992,
    imdb: { rating: 3.7 },
    lowestRatedThreeMoviesInYear: [
      'Children of the Corn II: The Final Sacrifice',
      'Stop! Or My Mom Will Shoot',
      'Shadowchaser'
    ]
  }
]

The preceding pipeline:

Uses the $match stage to filter the results to movies released in 1979, 1985, and 1992 with an IMDb rating of 4.5 or lower.
Uses $setWindowFields to partition movies by year and sort them by imdb.rating in descending order with title as a tiebreaker. The stage uses $lastN with n: 3 to create lowestRatedThreeMoviesInYear from a window defined as documents: [ "unbounded", "unbounded" ].
Uses $project to include only the title, year, imdb.rating, and lowestRatedThreeMoviesInYear fields in the output.
Uses $sort to sort the results by year, rating, and title.

Back

$last

$max