/ /

$first (window function)

Definition

$first

Returns the value of an expression for the first document in a window.

Note

Disambiguation

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

You can also use $first in these other contexts:

$first (accumulator), used in grouping to return the first value from each group.
$first (expression), which returns the first element of an array.

Syntax

{
   $setWindowFields: {
      ...
      output: {
         <field>: {
            $first: <expression>
         }
      }
   }
}

Behavior

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

$first converts missing field values to null.
$first returns null for empty windows.

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 identify the highest-rated movie in each year. Each document includes a field showing the title of the highest-rated movie released in that year:

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

[
  {
    imdb: { rating: 8.9 },
    year: 1999,
    title: 'Fight Club',
    topRatedMovieInYear: 'Fight Club'
  },
  {
    imdb: { rating: 8.7 },
    year: 1999,
    title: 'The Matrix',
    topRatedMovieInYear: 'Fight Club'
  },
  {
    title: 'Eddie Izzard: Dress to Kill',
    year: 1999,
    imdb: { rating: 8.7 },
    topRatedMovieInYear: 'Fight Club'
  },
  {
    imdb: { rating: 8.5 },
    year: 1999,
    title: 'The Green Mile',
    topRatedMovieInYear: 'Fight Club'
  },
  {
    imdb: { rating: 8.5 },
    year: 2000,
    title: 'Gladiator',
    topRatedMovieInYear: 'Gladiator'
  },
  {
    imdb: { rating: 8.5 },
    year: 2000,
    title: 'Memento',
    topRatedMovieInYear: 'Gladiator'
  },
  {
    title: 'Hera Pheri',
    year: 2000,
    imdb: { rating: 8.5 },
    topRatedMovieInYear: 'Gladiator'
  },
  {
    title: "A Dog's Will",
    year: 2000,
    imdb: { rating: 8.5 },
    topRatedMovieInYear: 'Gladiator'
  },
  {
    title: 'Band of Brothers',
    year: 2001,
    imdb: { rating: 9.6 },
    topRatedMovieInYear: 'Band of Brothers'
  },
  {
    title: 'The Blue Planet',
    year: 2001,
    imdb: { rating: 9.2 },
    topRatedMovieInYear: 'Band of Brothers'
  },
  {
    imdb: { rating: 8.8 },
    year: 2001,
    title: 'The Lord of the Rings: The Fellowship of the Ring',
    topRatedMovieInYear: 'Band of Brothers'
  },
  {
    imdb: { rating: 8.6 },
    year: 2001,
    title: 'Spirited Away',
    topRatedMovieInYear: 'Band of Brothers'
  },
  {
    title: 'Promises',
    year: 2001,
    imdb: { rating: 8.5 },
    topRatedMovieInYear: 'Band of Brothers'
  }
]

In the preceding pipeline:

$match filters for movies from 1999-2001 with an IMDb rating of 8.5 or higher.
$setWindowFields partitions movies by year and sorts them by imdb.rating in descending order. The stage uses $first with a window defined as documents: [ "unbounded", "unbounded" ] to return the title of the highest-rated movie in each year's partition.
$project includes only title, year, imdb.rating, and topRatedMovieInYear in the output.
$sort sorts the results by year and rating.

Back

$concatArrays

$firstN