如果没有文档，则MongoDB的汇总返回计数为0 [英] MongoDB aggregate return count of 0 if no documents

问题描述

我有一个MongoDB查询，该查询根据日期对5分钟的窗口进行分组并返回计数(这是使用count: { $sum: 1 }在该5分钟的窗口中的文档总数).

I have a MongoDB query that groups by 5min windows based on date and returns count (which is the total number of documents in that 5min window using count: { $sum: 1 }).

如果该组中不存在任何文档，我希望该查询还为特定的5分钟窗口返回0计数.但是，目前看来，仅返回具有正计数的组.

I'd like to have the query also return a count of 0 for a particular 5min window if no documents exist in that group. However currently, looks like only groups with a positive count are returned.

当前查询:

        const cursor = await collection.aggregate([
            { $sort : { time : 1 } },
            { 
                $match: {
                     $and: [ 
                        {selector: string },
                        {time: {$gte: timestamp }}
                     ]
                }
            },
            { 
                $group: {
                    _id: {
                        $subtract: [
                            { $subtract: [ "$time", 0 ] },
                            { $mod: [ 
                                { $subtract: [ "$time", 0 ] },
                                1000 * 60 * 5
                            ]}
                        ],
                    },
                    count: { $sum: 1 }
                }
            }
        ])

预期的响应:带有文档总数(包括总和0)的时间戳记

Expected response: timestamp with count of documents including sum 0

{ _id: 1525162000000, count: 314 }
{ _id: 1523144100000, count: 0 }
{ _id: 1512155500000, count: 54 }

提前谢谢！

推荐答案

免责声明:我不建议在服务器端(在MongoDB内部)这样做，而是在客户端处理这种情况.

Disclaimer: I do not recommend doing this on the server side (so inside MongoDB) but rather handle that case on the client side.

也就是说，这是您问题的通用解决方案，应该可以轻松地适应您的具体情况.

That said, here is a generic solution to your problem which should be easily adaptable to your specific case.

假设您有以下文档(或示例中的聚合管道输出):

Imagine you have the following documents (or output from an aggregation pipeline as in your example):

{
    "category" : 1
}
{
    "category" : 1
}
// note the missing { category: 2 } document here
{
    "category" : 3
}

下面的管道将创建空的存储桶(因此，在category字段中的值范围(本例中为2)中缺失的间隙"值的计数为0的文档):

The following pipeline will create empty buckets (so documents with a count of 0 for the "gap" values that are missing from the range of values in the category field - in this case the number 2):

var bucketSize = 1;

db.getCollection('test').aggregate({
    $group: {
        _id: null, // throw all documents into the same bucket
        "min": { $min: "$category" }, // just to calculate the lowest
        "max": { $max: "$category" }, // and the highest "category" value 
        "docs": { $push: "$$ROOT" } // and also keep the root documents
    }
}, {
    $addFields: {
        "docs": { // modify the existing docs array - created in the previous stage
            $concatArrays: [ // by concatenating
                "$docs", // the existing docs array
                {
                    $map: { // with some other array that will be generated
                        input: {
                            $range: [ "$min", "$max", bucketSize ] // based on the min and max values and the bucket size
                        },
                        as: "this",
                        in: { // but represented not as a plain number but as a document that effectively creates a bogus document
                            "category": "$$this", // the bogus category will be set to the respective value
                            "bogus": 1 // marker that allows us not to count this document in the next stage and still get a bucket from $group
                        }
                    }
                }
            ]
        }
    }
}, {
    $unwind: "$docs" // flatten the "docs" array which will now contain the bogus documents, too
}, {
    $group: {
        _id: "$docs.category", // group by category
        "count": { // this is the result we are interested in
            $sum: { // which will be aggregated by calculating the sum for each document of
                $cond: [ // either 0 or 1 per document
                    { $eq: [ "$docs.bogus", 1 ] }, // depending on whether the document should count as a result or not
                    0,
                    1
                ]
            }
        }
    }
})

上面查询的输出将是:

{
    "_id" : 2,
    "count" : 0.0 // this is what we wanted to achieve
}
{
    "_id" : 3,
    "count" : 1.0 // correct number of matches
}
{
    "_id" : 1,
    "count" : 2.0 // correct number of matches
}

这篇关于如果没有文档，则MongoDB的汇总返回计数为0的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！