Get a count of total documents with MongoDB when using limit
Solution 1
No, there is no other way. Two queries - one for count - one with limit. Or you have to use a different database. Apache Solr for instance works like you want. Every query there is limited and returns totalCount.
Solution 2
Mongodb 3.4 has introduced $facet
aggregation
which processes multiple aggregation pipelines within a single stage on the same set of input documents.
Using $facet
and $group
you can find documents with $limit
and can get total count.
You can use below aggregation in mongodb 3.4
db.collection.aggregate([
{ "$facet": {
"totalData": [
{ "$match": { }},
{ "$skip": 10 },
{ "$limit": 10 }
],
"totalCount": [
{ "$group": {
"_id": null,
"count": { "$sum": 1 }
}}
]
}}
])
Even you can use $count
aggregation which has been introduced in mongodb 3.6.
You can use below aggregation in mongodb 3.6
db.collection.aggregate([
{ "$facet": {
"totalData": [
{ "$match": { }},
{ "$skip": 10 },
{ "$limit": 10 }
],
"totalCount": [
{ "$count": "count" }
]
}}
])
Solution 3
MongoDB allows you to use cursor.count()
even when you pass limit()
or skip()
.
Lets say you have a db.collection
with 10 items.
You can do:
async function getQuery() {
let query = await db.collection.find({}).skip(5).limit(5); // returns last 5 items in db
let countTotal = await query.count() // returns 10-- will not take `skip` or `limit` into consideration
let countWithConstraints = await query.count(true) // returns 5 -- will take into consideration `skip` and `limit`
return { query, countTotal }
}
Solution 4
Here's how to do this with MongoDB 3.4+ (with Mongoose) using $facets
. This examples returns a $count
based on the documents after they have been matched.
const facetedPipeline = [{
"$match": { "dateCreated": { $gte: new Date('2021-01-01') } },
"$project": { 'exclude.some.field': 0 },
},
{
"$facet": {
"data": [
{ "$skip": 10 },
{ "$limit": 10 }
],
"pagination": [
{ "$count": "total" }
]
}
}
];
const results = await Model.aggregate(facetedPipeline);
This pattern is useful for getting pagination information to return from a REST API.
Reference: MongoDB $facet
Solution 5
Times have changed, and I believe you can achieve what the OP is asking by using aggregation with $sort
, $group
and $project
. For my system, I needed to also grab some user info from my users
collection. Hopefully this can answer any questions around that as well. Below is an aggregation pipe. The last three objects (sort, group and project) are what handle getting the total count, then providing pagination capabilities.
db.posts.aggregate([
{ $match: { public: true },
{ $lookup: {
from: 'users',
localField: 'userId',
foreignField: 'userId',
as: 'userInfo'
} },
{ $project: {
postId: 1,
title: 1,
description: 1
updated: 1,
userInfo: {
$let: {
vars: {
firstUser: {
$arrayElemAt: ['$userInfo', 0]
}
},
in: {
username: '$$firstUser.username'
}
}
}
} },
{ $sort: { updated: -1 } },
{ $group: {
_id: null,
postCount: { $sum: 1 },
posts: {
$push: '$$ROOT'
}
} },
{ $project: {
_id: 0,
postCount: 1,
posts: {
$slice: [
'$posts',
currentPage ? (currentPage - 1) * RESULTS_PER_PAGE : 0,
RESULTS_PER_PAGE
]
}
} }
])
bignay2000
Updated on July 05, 2022Comments
-
bignay2000 almost 2 years
I am interested in optimizing a "pagination" solution I'm working on with MongoDB. My problem is straight forward. I usually limit the number of documents returned using the
limit()
functionality. This forces me to issue a redundant query without thelimit()
function in order for me to also capture the total number of documents in the query so I can pass to that to the client letting them know they'll have to issue an additional request(s) to retrieve the rest of the documents.Is there a way to condense this into 1 query? Get the total number of documents but at the same time only retrieve a subset using
limit()
? Is there a different way to think about this problem than I am approaching it? -
mrechtien about 7 yearsIf you downvote, please add a reason so I have the chance to understand - which might also improve future answers!
-
Udit Kumawat about 7 yearsThis is not correct method.You are just finding in all document instead of first 10 documents in each request.For each request,everytime you are just finding in whole documents. not in first 10.
-
Vibhu Tewary about 7 yearsthanks for the comment. at the time this is a solution we came up with. it may not be perfect when it comes to efficiency. do suggest a solution to improvise.
-
Kumar over 6 yearsWhat will be the response for this query. Will it return count as well as result
-
TestWell over 6 years@Kumar yes, the count is calculated during $group using $sum and the array result comes from $push. You can see in the $project that I include the post count (postCount) then take only a section from the result array using $slice. The final response returns the number of total posts along with only a section of them for pagination.
-
Felipe about 6 yearsI'm not sure about the downvote but just an FYI:
count()
only works withfind()
and thus is not helpful withaggregate
queries -
Felipe about 6 yearsI'm not sure if "No" is quite the answer anymore now that we have mongoDb 3.4. See stackoverflow.com/a/39784851/3654061
-
Mahmoud Heretani almost 6 yearshow about aggregate ?
-
Sunil Pachlangia over 5 yearsJust little update total should be an array like total: [{ $count: 'total' }]
-
Sam Gruse over 5 yearsThere are multiple ways of doing this as I've been search for a solution myself. You can create an aggregation operation to return the total count as well as full documents according to a condition. You can also do one findAll based on conditions. Store the length of that array. And then slice out values according to your limit / offset values. Both of these options are only one call to the DB. The expense of the aggregation depends on how complex it is, same with the slice that you run on the returned array. Thoughts on this?
-
TOPKAT almost 5 yearsBest one for me, I hate aggregations ^^. I find this way more simple and readable.
-
sznrbrt over 4 yearsHow about this answer? stackoverflow.com/a/56693959 for me seems to work. Compared to aggregation with a limit of 100 docs, runs even slightly (~2-3ms) faster on avg for me...
-
Walter Tross about 4 years
.skip(5).limit(5)
does NOT return the last 5 items in the DB. It returns the second group of 5 items.count()
will always return 10 no matter how many items there are, provided there are at least 10. -
Beingnin over 3 yearscheck out the implementation beingnin.medium.com/…
-
Hedley Smith about 3 yearsIf you're looking to get a total count of the data after the $match has happened, placing the $match before the $facet achieves this.
-
Ricky-U almost 3 yearsWhy is countTotal and CountWithConstraints awaiting a promise?
-
teuber789 over 2 yearsThis answer explains the same thing but much clearer.
-
Rigin Oommen over 2 yearsTHis answer returns the total count properly but its failing to return the totalData
-
Alok Deshwal over 2 yearsnot working with $sort stage, getting unexpected output. Issue is only exist if using $facet.
-
Amit Kumar over 2 yearsMongo version 4.4 & mongo node client version 4 doesn't show total item count.