Getting count of total documents when using aggregation along with skip and limit

Question

How can I get the total count of documents when using the aggregation along with limit and skip like in the following query?

  db.Vote.aggregate({
       $match: {
           tid: "e6d38e1ecd",
           "comment.topic": {$exists: 1},
       }
   },{
       $group: {
           _id: {
               topic: "$comment.topic",
               text_sentiment: "$comment.text_sentiment"
               
           },
           total: {$sum: 1},
           
       }
   },{
       $project: {
           topic: {
               name: "$_id.topic",
               occurence: "$total"
           },
           sentiment: "$_id.text_sentiment"
       }
   },{
       $sort: {"topic.occurence": -1}
   })
    .skip(SKIP)
    .limit(LIMIT)

Here I would always get the documents bounded by LIMIT, but at each query, I also want to know the total documents. How can I do that?

EDIT While some of you have suggested using $facet, I am not sure how to use that. For example, the aggregation returns 50 documents without limit. How can I use $facet such that it not only returns LIMIT documents but also the meta-data that contains the total documents.

Here is a working query from MongoDB Playground that I created. How can I use $facet or any other method to get the total count.

Or maybe this one https://stackoverflow.com/questions/51029987/using-count-within-an-addfield-operation-in-mongodb-aggregation/51030473#51030473 — Cristian-Florin Calina, Jul 02 '22 at 11:01
@Cristian-FlorinCalina Not able to understand. Do I need to copy the same aggregation code twice to get the count using `$facet`? Could you give an example? — Suhail Gupta, Jul 02 '22 at 11:11
No, you can add the `$facet` at the end, but the disadvantage is that it will group all your documents to one document, Hence I prefer the `$setWindowFeilds` — nimrod serok, Jul 02 '22 at 11:14
This was my answer to the same question https://stackoverflow.com/questions/48305624/how-to-use-mongodb-aggregation-for-pagination/48307554#48307554 evidently it was answered many times already — Alex Blex, Jul 02 '22 at 11:19

nimrod serok · Accepted Answer · 2022-07-02T11:28:05.767

2

One option is using $facet, but the disadvantage is that it will group all your documents to one document:

db.Vote.aggregate(
  {$match: {tid: "e6d38e1ecd",  "comment.topic": {$exists: 1}}},
  {$group: {_id: {topic: "$comment.topic", text_sentiment: "$comment.text_sentiment"},
           total: {$sum: 1}}
   },
  {$project: {topic: {name: "$_id.topic",  occurence: "$total"},
           sentiment: "$_id.text_sentiment"}
  },
  {$facet: {
    data: [{$sort: {"topic.occurence": -1}}, {$skip: SKIP}, {$limit: LIMIT}],
    total: [{ $count: "total" }]
  }}
)

Other option is to use $setWindowFields to add the total count to each document, which allows to keep them as separate documents:

db.Vote.aggregate(
  {$match: {tid: "e6d38e1ecd",  "comment.topic": {$exists: 1}}},
  {$group: {_id: {topic: "$comment.topic", text_sentiment: "$comment.text_sentiment"},
           total: {$sum: 1}}
   },
  {$project: {topic: {name: "$_id.topic",  occurence: "$total"},
           sentiment: "$_id.text_sentiment"}
  },
  {
    $setWindowFields: {output: {totalCount: {$count: {}}}}
  })
    .skip(SKIP)
    .limit(LIMIT)

edited Jul 02 '22 at 11:28

answered Jul 02 '22 at 11:20

nimrod serok

14,151
2
11
33

Could you please explain `setWindowFields`? What is happening here `{output: {totalCount: {$count: {}}}}`? – Suhail Gupta Jul 02 '22 at 11:24
The `totalCount` field is being added to all documents with the same value: the total count. This allows you to NOT group all your documents into one big document, which is important since documents have size limitation – nimrod serok Jul 02 '22 at 11:27
Does the statement `$count: {}` mean, get whatever is there in the output? – Suhail Gupta Jul 02 '22 at 11:29
1

It means count the number of documents in this phase – nimrod serok Jul 02 '22 at 11:34
Could you please explain what does `total: [{ $count: "total" }]` mean inside `facet`? What does `$count: "total"` mean? What is `total`? – Suhail Gupta Jul 19 '22 at 07:33
"total" in this case is a string which will be the key name for the value of total. See [`$count`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/count/) – nimrod serok Jul 19 '22 at 08:49

Getting count of total documents when using aggregation along with skip and limit

1 Answers1

Linked