/ /

$count (window function)

Definition

$count

Returns the number of documents in a particular window.

Note

Disambiguation

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

You can also use $count in these other contexts:

$count (accumulator), which returns the number of documents in a group.
$count (aggregation stage), which returns the number of input documents.

Syntax

{
   $setWindowFields: {
      ...
      output: {
         <field>: {
            $count: {},
            window: {
               documents: [ <lower boundary>, <upper boundary> ]
            }
         }
      }
   }
}

$count does not accept any parameters.

Tip

$count is functionally equivalent to using { $sum: 1 } in the $setWindowFields stage.

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.

Count Movies with a Similar Rating

This example uses the $count window function with a range-based window to count how many Action movies from 2000 have an IMDb rating within 0.5 points of each movie's rating.

db.movies.aggregate( [
   {
      $match: {
         genres: "Action",
         year: 2000,
         "imdb.rating": { $ne: null }
      }
   },
   {
      $setWindowFields: {
         sortBy: { "imdb.rating": 1 },
         output: {
            moviesWithSimilarRating: {
               $count: {},
               window: {
                  range: [ -0.5, 0.5 ]
               }
            }
         }
      }
   },
   {
      $project: {
         _id: 0,
         title: 1,
         "imdb.rating": 1,
         moviesWithSimilarRating: 1
      }
   },
   { $sort: { "imdb.rating": -1 } },
   { $limit: 10 }
] )

[
  { title: 'Gladiator', imdb: { rating: 8.5 }, moviesWithSimilarRating: 4 },
  { title: "Bruce Lee: A Warrior's Journey", imdb: { rating: 8.2 }, moviesWithSimilarRating: 7 },
  { title: 'Forklift Driver Klaus: The First Day on the Job', imdb: { rating: 8 }, moviesWithSimilarRating: 7 },
  { title: 'Forklift Driver Klaus: The First Day on the Job', imdb: { rating: 8 }, moviesWithSimilarRating: 7 },
  { title: 'Crouching Tiger, Hidden Dragon', imdb: { rating: 7.9 }, moviesWithSimilarRating: 8 },
  { title: 'Batman Beyond: Return of the Joker', imdb: { rating: 7.9 }, moviesWithSimilarRating: 8 },
  { title: 'Flickering Lights', imdb: { rating: 7.7 }, moviesWithSimilarRating: 9 },
  { title: 'X-Men', imdb: { rating: 7.4 }, moviesWithSimilarRating: 12 },
  { title: 'X-Men', imdb: { rating: 7.4 }, moviesWithSimilarRating: 12 },
  { title: 'Killer Bean 2: The Party', imdb: { rating: 7.3 }, moviesWithSimilarRating: 12 }
]

In the preceding example:

$match filters for Action movies released in 2000 that have an imdb.rating value.
sortBy: { "imdb.rating": 1 } sorts documents by IMDb rating in ascending order. A sortBy is required when using a range-based window.
output uses $count to set the moviesWithSimilarRating field to the number of movies whose IMDb rating falls within the current window.

The window uses range: [ -0.5, 0.5 ], which includes all documents whose imdb.rating is within 0.5 points of the current document's rating.
$project includes the title, imdb.rating, and moviesWithSimilarRating fields in the output.
$sort and $limit return the 10 highest-rated movies for readability.

The output shows that Gladiator (8.5), with a relatively unique rating, has a lower moviesWithSimilarRating count than movies with more common ratings such as 7.4.

Back

$bottomN

$concatArrays