Multiple aggregation pipeline in a single query in MongoDB | Multiple Counts with a single query in MongoDB

Many time we want run query with in query to get desirable result. MongoDB provide a aggregation operator to achieve result like this. Lets assume we have a collection with documents similar in structure to below : (this example from StackOverflow)

_id: xyz
Movieid: 123
MovieName: Titanic
ReleaseDate: 2000-01-01

_id: uvw
Movieid: 456
MovieName: Titanic II
ReleaseDate: 2018-01-01

_id: pqr
Movieid: 789
MovieName: Titanic III

I would like to achieve the output as counts for total movies, movies with the release date, and movies without a release date in 3 separate columns as below:

Total   |   Released  |     UnReleased
 3      |       2     |          1

To achieve the above result we need to run multiple aggregation pipelines within the same query. To do this MongoDB provide us an operator named $facet. This can be used to run multiple pipelines with the same data.

$facet (3.4+):

Processes multiple aggregation pipelines within a single stage on the same set of input documents. Each sub-pipeline has its own field in the output document where its results are stored as an array of documents.

{ $facet:
      <outputField1>: [ <stage1>, <stage2>, ... ],
      <outputField2>: [ <stage1>, <stage2>, ... ],


The $facet stage, and its sub-pipelines, cannot make use of indexes, even if its sub-pipelines use $match or if $facet is the first stage in the pipeline. The $facet stage will always perform a COLLSCAN during execution.

Input documents are passed to the $facet stage only once. $facet enables various aggregations on the same set of input documents, without needing to retrieve the input documents multiple times.


  { "$facet": {
    "Total": [
      { "$match" : { "ReleaseDate": { "$exists": true }}},
      { "$count": "Total" },
    "Released": [
      { "$match" : {"ReleaseDate": { "$exists": true, "$nin": [""] }}},
      { "$count": "Released" }
    "Unreleased": [
      { "$match" : {"ReleaseDate": { "$exists": true, "$in": [""] }}},
      { "$count": "Unreleased" }
  { "$project": {
    "Total": { "$arrayElemAt": ["$Total.Total", 0] },
    "Released": { "$arrayElemAt": ["$Released.Released", 0] },
    "Unreleased": { "$arrayElemAt": ["$Unreleased.Unreleased", 0] }

// output
    "Total": 3,
    "Released": 2,
    "Unreleased": 1

In each pipeline we can use all aggregation related operator to achieve desire result.

Leave a Reply