/ /

$min (Window Function)

Definition

$min

Returns the minimum value from documents in a particular window.

Note

Disambiguation

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

You can also use $min in these other contexts:

$min (accumulator), used in grouping to return the minimum value from a group of documents.
$min (expression), which returns the minimum value from an array.

Syntax

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

Behavior

Result Type

When $min compares values of different types, it uses the BSON comparison order to determine which value is smaller.

Null or Missing Values

If some documents in the window have a null value for the field or are missing the field, $min ignores them. It considers only the non-null and non-missing values.

If all documents in the window have a null value for the field or are missing the field, $min returns 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 add a field to each document containing the IMDb rating of the worst-rated movie released in the same year:

db.movies.aggregate( [
   {
      $match: {
         year: { $in: [ 1999, 2000, 2001 ] },
         "imdb.rating": { $lte: 3.5 }
      }
   },
   {
      $setWindowFields: {
         partitionBy: "$year",
         sortBy: { title: 1 },
         output: {
            minimumRatingInYear: {
               $min: "$imdb.rating",
               window: {
                  documents: [ "unbounded", "unbounded" ]
               }
            },
            rankInYear: {
               $documentNumber: {}
            }
         }
      }
   },
   {
      $project: {
         _id: 0,
         title: 1,
         year: 1,
         imdb: { rating: "$imdb.rating" },
         minimumRatingInYear: 1,
         rankInYear: 1
      }
   },
   { $sort: { year: 1, title: 1 } }
] )

[
  {
    title: 'The Item',
    year: 1999,
    imdb: { rating: 3.4 },
    minimumRatingInYear: 2.5,
    rankInYear: 1
  },
  {
    title: 'The Underground Comedy Movie',
    year: 1999,
    imdb: { rating: 2.5 },
    minimumRatingInYear: 2.5,
    rankInYear: 2
  },
  {
    imdb: { rating: 2.4 },
    year: 2000,
    title: 'Battlefield Earth',
    minimumRatingInYear: 2.4,
    rankInYear: 1
  },
  {
    title: 'Thomas and the Magic Railroad',
    year: 2000,
    imdb: { rating: 3.5 },
    minimumRatingInYear: 2.4,
    rankInYear: 2
  },
  {
    imdb: { rating: 2.1 },
    year: 2001,
    title: 'Glitter',
    minimumRatingInYear: 2.1,
    rankInYear: 1
  },
  {
    title: 'The Gaul',
    year: 2001,
    imdb: { rating: 2.7 },
    minimumRatingInYear: 2.1,
    rankInYear: 2
  },
  {
    title: 'The Hexer',
    year: 2001,
    imdb: { rating: 3.5 },
    minimumRatingInYear: 2.1,
    rankInYear: 3
  },
  {
    title: 'Ticker',
    year: 2001,
    imdb: { rating: 3.5 },
    minimumRatingInYear: 2.1,
    rankInYear: 4
  },
  {
    title: 'Wishmaster 3: Beyond the Gates of Hell',
    year: 2001,
    imdb: { rating: 3.5 },
    minimumRatingInYear: 2.1,
    rankInYear: 5
  }
]

The preceding pipeline:

Uses $match to filter to movies from 1999, 2000, and 2001 with an IMDb rating of 3.0 or lower.
Uses $setWindowFields to partition movies by year and sort them by title. The stage uses $min with a documents window of [ "unbounded", "unbounded" ] to create minimumRatingInYear, which holds the minimum imdb.rating across the entire partition, and $documentNumber to assign a per-partition row number (rankInYear) to each document.
Uses $project to include title, year, imdb, minimumRatingInYear, and rankInYear in the output.
Uses $sort to sort the results by year and title.

Back

$mergeObjects

$minN