MongoDB 聚合：如何获得总记录数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20348093/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MongoDB Aggregation: How to get total records count?
提问by user2987836
I have used aggregation for fetching records from mongodb.
我使用聚合从 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
如果我无限制地执行此查询，则将获取 10 条记录。但我想将限制保持为 2。所以我想获得总记录数。我该如何处理聚合？请给我建议。谢谢
回答by Anurag pareek
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": [
{...},
{...},
{...},
]
}
]
回答by user3658510
Since v.3.4 (i think) MongoDB has now a new aggregation pipeline operator named 'facet' which in their own words:
从 v.3.4（我认为）开始，MongoDB 现在有了一个名为“ facet”的新聚合管道运算符，用他们自己的话来说：
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):
结果将是（对于前 100 个总结果）：
[
{
"paginatedResults":[{...},{...},{...}, ...],
"totalCount":[{"count":100}]
}
]
回答by Vishal Ranapariya
Use this to find total count in resulting collection.
使用它来查找结果集合中的总计数。
db.collection.aggregate( [
{ $match : { score : { $gt : 70, $lte : 90 } } },
{ $group: { _id: null, count: { $sum: 1 } } }
] );
回答by Ankit Arya
You can use toArray function and then get its length for total records count.
您可以使用 toArray 函数，然后获取其总记录数的长度。
db.CollectionName.aggregate([....]).toArray().length
回答by cnsnaveen
Use the $count aggregation pipeline stageto get the total document count:
使用$count 聚合管道阶段获取总文档数：
Query :
询问 ：
db.collection.aggregate(
[
{
$match: {
...
}
},
{
$group: {
...
}
},
{
$count: "totalCount"
}
]
)
Result:
结果：
{
"totalCount" : Number of records (some integer value)
}
回答by mad Man
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);
回答by Filip Voska
Solution provided by @Divergent does work, but in my experience it is better to have 2 queries:
@Divergent 提供的解决方案确实有效，但根据我的经验，最好有 2 个查询：
- 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.
- 首先进行过滤，然后按 ID 分组以获得过滤元素的数量。不要在这里过滤，这是不必要的。
- 过滤、排序和分页的第二个查询。
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).
推送 $$ROOT 并使用 $slice 的解决方案遇到了 16MB 的大型集合的文档内存限制。此外，对于大型集合，两个查询一起运行似乎比使用 $$ROOT 推送的查询运行得更快。您也可以并行运行它们，因此您只会受到两个查询中较慢的查询（可能是排序的查询）的限制。
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):
我已经使用 2 个查询和聚合框架解决了这个解决方案（注意 - 我在这个例子中使用 node.js，但想法是一样的）：
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
});
}
});
}
});
});
回答by Harpal Singh
//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});
回答by Rohit Parte
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) => {})
回答by miqrc
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
}
