MongoDB Aggregation: How to get total records count?

Question

I have used aggregation for fetching records from mongodb.

$result = $collection->aggregate(array(
  array('$match' => $document),
  array('$group' => array('_id' => '$book_id', 'date' => array('$max' => '$book_viewed'),  'views' => array('$sum' => 1))),
  array('$sort' => $sort),
  array('$skip' => $skip),
  array('$limit' => $limit),
));

If I execute this query without limit then 10 records will be fetched. But I want to keep limit as 2. So I would like to get the total records count. How can I do with aggregation? Please advice me. Thanks

Take a look at $facet This may help https://stackoverflow.com/questions/61812361/how-do-i-count-all-the-documents-in-a-collection-and-use-the-cont-in-a-controlle/61813062#61813062 — Soham, May 15 '20 at 06:38
Watch for those users of `'$geoNear'`, because `'$facet'` is not compatible with it. — Fabien Haddadi, Sep 28 '21 at 13:34

score 178 · Answer 1 · edited Nov 11 '21 at 18:21

178

Since v.3.4 (i think) MongoDB has now a new aggregation pipeline operator named 'facet' which in their own words:

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.

In this particular case, this means that one can do something like this:

$result = $collection->aggregate([
  { ...execute queries, group, sort... },
  { ...execute queries, group, sort... },
  { ...execute queries, group, sort... },
  {
    $facet: {
      paginatedResults: [{ $skip: skipPage }, { $limit: perPage }],
      totalCount: [
        {
          $count: 'count'
        }
      ]
    }
  }
]);

The result will be (with for ex 100 total results):

[
  {
    "paginatedResults":[{...},{...},{...}, ...],
    "totalCount":[{"count":100}]
  }
]

edited Nov 11 '21 at 18:21

Iain J. Reid

978
1
11
23

answered Mar 26 '18 at 04:19

user3658510

2,094
1
14
12

22

This works great, as of 3.4 this should be the accepted answer – Adam Reis Jul 26 '18 at 01:48
2

To convert so arrayful result into simple two field object I need another `$project`? – SerG Feb 17 '19 at 21:54
17

This should be the accepted answer today. However, I found performance issues when using paging with $facet. The other up voted answer also has performance issues with $slice. I found it better to $skip and $limit in the pipeline and make a separate call for count. I tested this against fairly large data sets. – Jpepper Jun 18 '19 at 18:55
1

@SerG you can $unwind the totalCount – HasaniH Jan 18 '21 at 21:15
Regarding performance, 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. – Prisacari Dmitrii Oct 07 '22 at 21:35
@PrisacariDmitrii is this a concern for the solution above, when you only use skip, limit and count? – Asu Aug 18 '23 at 00:59

score 113 · Answer 2 · answered Sep 30 '16 at 06:15

113

This is one of the most commonly asked question to obtain the paginated result and the total number of results simultaneously in single query. I can't explain how I felt when I finally achieved it LOL.

$result = $collection->aggregate(array(
  array('$match' => $document),
  array('$group' => array('_id' => '$book_id', 'date' => array('$max' => '$book_viewed'),  'views' => array('$sum' => 1))),
  array('$sort' => $sort),

// get total, AND preserve the results
  array('$group' => array('_id' => null, 'total' => array( '$sum' => 1 ), 'results' => array( '$push' => '$$ROOT' ) ),
// apply limit and offset
  array('$project' => array( 'total' => 1, 'results' => array( '$slice' => array( '$results', $skip, $length ) ) ) )
))

Result will look something like this:

[
  {
    "_id": null,
    "total": ...,
    "results": [
      {...},
      {...},
      {...},
    ]
  }
]

answered Sep 30 '16 at 06:15

Anurag pareek

1,382
1
10
21

9

Documentation on this: https://docs.mongodb.com/v3.2/reference/operator/aggregation/group/#group-documents-by-author ... note that with this approach, the entire non-paginated result set must fit in 16MB. – btown Nov 15 '16 at 17:56
9

This is pure gold! I was going thru hell trying to make this work. – Henrique Miranda Nov 30 '16 at 14:36
4

Thanks guy ! I juste need `{ $group: { _id: null, count: { $sum:1 }, result: { $push: '$$ROOT' }}}` (insert after `{$group:{}}` for count total find. – Liberateur Jun 22 '17 at 09:11
2

How do you apply limit to the results set? Results is now a nested array – valen Jan 07 '18 at 18:53
@valen You can see last line of code " 'results' => array( '$slice' => array( '$results', $skip, $length ) )" Here you can apply limit and skip params – Anurag pareek Jan 08 '18 at 16:41

score 72 · Answer 3 · edited Feb 14 '20 at 04:09

72

Use this to find total count in resulting collection.

db.collection.aggregate( [
{ $match : { score : { $gt : 70, $lte : 90 } } },
{ $group: { _id: null, count: { $sum: 1 } } }
] );

edited Feb 14 '20 at 04:09

Naman

27,789
26
218
353

answered Dec 03 '13 at 10:19

Vishal Ranapariya

1,026
8
8

3

Thanks. But, I have used "views" in my coding to get the count of the corresponding group count(i.e, group 1 => 2 records, group 3 => 5 records & so on). I want to get the records count(i.e, total: 120 records). Hope you understood.. – user2987836 Dec 03 '13 at 10:24
Tricki one: { $sum: 1 } – Honcharov Anton Jan 13 '23 at 11:27

score 50 · Answer 4 · answered Apr 25 '17 at 06:31

50

You can use toArray function and then get its length for total records count.

db.CollectionName.aggregate([....]).toArray().length

answered Apr 25 '17 at 06:31

Ankit Arya

872
7
10

1

While this might not work as a "proper" solution, it helped me debug something - it does work, even if it's not 100% a solution. – Johann Marx Nov 08 '17 at 09:18
5

This isn't real solution. – Furkan Başaran Feb 19 '19 at 10:59
3

`TypeError: Parent.aggregate(...).toArray is not a function` this is the error I gave with this solution. – Mohammad Hossein Shojaeinia Apr 16 '19 at 11:24
2

This will fetch all the aggregated data then return the length of that array. not a good practice . instead u can add {$count: 'count'} in aggregation pipeline – Aslam Shaik Feb 28 '20 at 12:04
2

Not sure that loading all the results is a smart idea to retrieve the totalCount. – Mark Odey Apr 07 '21 at 14:48
It's not the correct response for that question but helped me in another issue. Thanks. – Alexandre N. Apr 09 '21 at 17:24

score 35 · Answer 5 · answered Jun 30 '20 at 05:54

Here are some ways to get total records count while doing MongoDB Aggregation:

Using $count:
```
db.collection.aggregate([
   // Other stages here
   { $count: "Total" }
])
```
For getting 1000 records this takes on average 2 ms and is the fastest way.

Using .toArray():
```
db.collection.aggregate([...]).toArray().length
```
For getting 1000 records this takes on average 18 ms.

Using .itcount():
```
db.collection.aggregate([...]).itcount()
```
For getting 1000 records this takes on average 14 ms.

score 33 · Answer 6 · edited Jun 28 '19 at 08:14

33

Use the $count aggregation pipeline stage to get the total document count:

Query :

db.collection.aggregate(
  [
    {
      $match: {
        ...
      }
    },
    {
      $group: {
        ...
      }
    },
    {
      $count: "totalCount"
    }
  ]
)

Result:

{
   "totalCount" : Number of records (some integer value)
}

edited Jun 28 '19 at 08:14

Dan Dascalescu

143,271
52
317
404

answered Jul 17 '18 at 13:33

cnsnaveen

345
3
9

1

This works just like a charm, but performance-wise is it good? – ana.arede Sep 20 '18 at 18:06
Will return empty array on no matches. – Oliver Dixon May 25 '22 at 10:38

score 13 · Answer 7 · edited Jul 04 '17 at 12:03

13

I did it this way:

db.collection.aggregate([
     { $match : { score : { $gt : 70, $lte : 90 } } },
     { $group: { _id: null, count: { $sum: 1 } } }
] ).map(function(record, index){
        print(index);
 });

The aggregate will return the array so just loop it and get the final index .

And other way of doing it is:

var count = 0 ;
db.collection.aggregate([
{ $match : { score : { $gt : 70, $lte : 90 } } },
{ $group: { _id: null, count: { $sum: 1 } } }
] ).map(function(record, index){
        count++
 }); 
print(count);

edited Jul 04 '17 at 12:03

nj-ath

3,028
2
25
41

answered Jun 27 '17 at 05:34

mad Man

366
3
7

fwiw you don't need the `var` declaration nor the `map` call. The first 3 lines of your first example is sufficient. – Madbreaks Apr 04 '18 at 23:56

score 10 · Answer 8 · answered Aug 26 '19 at 01:50

//const total_count = await User.find(query).countDocuments();
//const users = await User.find(query).skip(+offset).limit(+limit).sort({[sort]: order}).select('-password');
const result = await User.aggregate([
  {$match : query},
  {$sort: {[sort]:order}},
  {$project: {password: 0, avatarData: 0, tokens: 0}},
  {$facet:{
      users: [{ $skip: +offset }, { $limit: +limit}],
      totalCount: [
        {
          $count: 'count'
        }
      ]
    }}
  ]);
console.log(JSON.stringify(result));
console.log(result[0]);
return res.status(200).json({users: result[0].users, total_count: result[0].totalCount[0].count});

It is usually good practice to include explanatory text along with a code answer. — , Aug 26 '19 at 02:16

Filip Voska · Answer 9 · 2017-02-09T17:52:36.717

Solution provided by @Divergent does work, but in my experience it is better to have 2 queries:

First for filtering and then grouping by ID to get number of filtered elements. Do not filter here, it is unnecessary.
Second query which filters, sorts and paginates.

Solution with pushing $$ROOT and using $slice runs into document memory limitation of 16MB for large collections. Also, for large collections two queries together seem to run faster than the one with $$ROOT pushing. You can run them in parallel as well, so you are limited only by the slower of the two queries (probably the one which sorts).

I have settled with this solution using 2 queries and aggregation framework (note - I use node.js in this example, but idea is the same):

var aggregation = [
  {
    // If you can match fields at the begining, match as many as early as possible.
    $match: {...}
  },
  {
    // Projection.
    $project: {...}
  },
  {
    // Some things you can match only after projection or grouping, so do it now.
    $match: {...}
  }
];


// Copy filtering elements from the pipeline - this is the same for both counting number of fileter elements and for pagination queries.
var aggregationPaginated = aggregation.slice(0);

// Count filtered elements.
aggregation.push(
  {
    $group: {
      _id: null,
      count: { $sum: 1 }
    }
  }
);

// Sort in pagination query.
aggregationPaginated.push(
  {
    $sort: sorting
  }
);

// Paginate.
aggregationPaginated.push(
  {
    $limit: skip + length
  },
  {
    $skip: skip
  }
);

// I use mongoose.

// Get total count.
model.count(function(errCount, totalCount) {
  // Count filtered.
  model.aggregate(aggregation)
  .allowDiskUse(true)
  .exec(
  function(errFind, documents) {
    if (errFind) {
      // Errors.
      res.status(503);
      return res.json({
        'success': false,
        'response': 'err_counting'
      });
    }
    else {
      // Number of filtered elements.
      var numFiltered = documents[0].count;

      // Filter, sort and pagiante.
      model.request.aggregate(aggregationPaginated)
      .allowDiskUse(true)
      .exec(
        function(errFindP, documentsP) {
          if (errFindP) {
            // Errors.
            res.status(503);
            return res.json({
              'success': false,
              'response': 'err_pagination'
            });
          }
          else {
            return res.json({
              'success': true,
              'recordsTotal': totalCount,
              'recordsFiltered': numFiltered,
              'response': documentsP
            });
          }
      });
    }
  });
});

score 3 · Answer 10 · answered Aug 17 '19 at 07:35

This could be work for multiple match conditions

            const query = [
                {
                    $facet: {
                    cancelled: [
                        { $match: { orderStatus: 'Cancelled' } },
                        { $count: 'cancelled' }
                    ],
                    pending: [
                        { $match: { orderStatus: 'Pending' } },
                        { $count: 'pending' }
                    ],
                    total: [
                        { $match: { isActive: true } },
                        { $count: 'total' }
                    ]
                    }
                },
                {
                    $project: {
                    cancelled: { $arrayElemAt: ['$cancelled.cancelled', 0] },
                    pending: { $arrayElemAt: ['$pending.pending', 0] },
                    total: { $arrayElemAt: ['$total.total', 0] }
                    }
                }
                ]
                Order.aggregate(query, (error, findRes) => {})

score 2 · Answer 11 · answered Dec 28 '19 at 15:36

I needed the absolute total count after applying the aggregation. This worked for me:

db.mycollection.aggregate([
    {
        $group: { 
            _id: { field1: "$field1", field2: "$field2" },
        }
    },
    { 
        $group: { 
            _id: null, count: { $sum: 1 } 
        } 
    }
])

Result:

{
    "_id" : null,
    "count" : 57.0
}

score 2 · Answer 12 · answered Oct 31 '22 at 10:23

Here is an example with Pagination, match and sort in mongoose aggregate

const [response] = await Prescribers.aggregate([
      { $match: searchObj },
      { $sort: sortObj },
      {
        $facet: {
          response: [{ $skip: count * page }, { $limit: count }],
          pagination: [
            {
              $count: 'totalDocs',
            },
            {
              $addFields: {
                page: page + 1,
                totalPages: {
                  $floor: {
                    $divide: ['$totalDocs', count],
                  },
                },
              },
            },
          ],
        },
      },
    ]);

Here count is the limit of each page and page is the the page number. Prescribers is the model

This would return the records similar to this

"data": {
    "response": [
        {
            "_id": "6349308c90e58c6820bbc682",
            "foo": "bar"
        }
        {
            "_id": "6349308c90e58c6820bbc682",
            "foo": "bar"
        },
        {
            "_id": "6349308c90e58c6820bbc682",
            "foo": "bar"
        }
        {
            "_id": "6349308c90e58c6820bbc682",
            "foo": "bar"
        },
        {
            "_id": "6349308c90e58c6820bbc682",
            "foo": "bar"
        },
        {
            "_id": "6349308c90e58c6820bbc682",
            "foo": "bar"
        }
        {
            "_id": "6349308c90e58c6820bbc682",
            "foo": "bar"
        },
        {
            "_id": "6349308c90e58c6820bbc682",
            "foo": "bar"
        }
        {
            "_id": "6349308c90e58c6820bbc682",
            "foo": "bar"
        },
        {
            "_id": "6349308c90e58c6820bbc682",
            "foo": "bar"
        },
    ],
    "pagination": [
        {
            "totalDocs": 592438,
            "page": 1,
            "totalPages": 59243
        }
    ]
}

score 1 · Answer 13 · answered Mar 24 '20 at 10:20

1

If you don't want to group, then use the following method:

db.collection.aggregate( [ { $match : { score : { $gt : 70, $lte : 90 } } }, { $count: 'count' } ] );

answered Mar 24 '20 at 10:20

Rajan Sharma

11
3

I think the person asking the question does want to group, based on the subject. – mjaggard Mar 24 '20 at 10:52

score 1 · Answer 14 · answered Jul 19 '23 at 17:40

1

It will first match and then give the matching condition total document count even your document count greater than 100k.

[
  {
    $match: {
      "Document.CompanyId": 12345
    }
  },
  {
    $count: 'Total_Count'
  }
]

answered Jul 19 '23 at 17:40

Dinesh

2,744
1
12
12

score 0 · Answer 15 · edited May 23 '17 at 10:31

0

Sorry, but I think you need two queries. One for total views and another one for grouped records.

You can find useful this answer

edited May 23 '17 at 10:31

Community

1
1

answered Dec 03 '13 at 12:42

rubenfa

831
1
7
23

Thanks..I think so..But, there is no option with aggregation.. :( – user2987836 Dec 03 '13 at 12:47
1

i ran in to a similar situation. There was no answer but to do 2 querys. :( http://stackoverflow.com/questions/20113731/mongo-aggeregation-limit-while-grouping/20114288 – astroanu Dec 03 '13 at 13:31

score 0 · Answer 16 · answered Feb 11 '22 at 16:50

if you need to $match with nested documents then

https://mongoplayground.net/p/DpX6cFhR_mm

db.collection.aggregate([
  {
    "$unwind": "$tags"
  },
  {
    "$match": {
      "$or": [
        {
          "tags.name": "Canada"
        },
        {
          "tags.name": "ABC"
        }
      ]
    }
  },
  {
    "$group": {
      "_id": null,
      "count": {
        "$sum": 1
      }
    }
  }
])

score 0 · Answer 17 · answered Jul 04 '22 at 07:46

I had to perform a lookup, match and then count the documents recieved. Here is how I achieved it using mongoose:

ModelName.aggregate([
  {
    '$lookup': {
      'from': 'categories', 
      'localField': 'category', 
      'foreignField': '_id', 
      'as': 'category'
    }
  }, {
    '$unwind': {
      'path': '$category'
    }
  }, {
    '$match': {
      'category.price': {
        '$lte': 3, 
        '$gte': 0
      }
    }
  }, {
    '$count': 'count'
  }
]);

MongoDB Aggregation: How to get total records count?

17 Answers17

Using `$count`:

Using `.toArray()`:

Using `.itcount()`:

Linked

Related

MongoDB Aggregation: How to get total records count?

17 Answers17

Using $count:

Using .toArray():

Using .itcount():

Linked

Related

Using `$count`:

Using `.toArray()`:

Using `.itcount()`: