如果没有结果,则MongoDB的总返回计数为0 [英] MongoDB aggregate return count of 0 if no results
问题描述
我有以下MongoDB查询,该查询按日期和结果分组并计数.我想让查询还返回特定日期和结果的计数为0(如果当天没有数据).
I have the following MongoDB query that groups by date and result and gives a count. I'd like to have the query also return a count of 0 for a particular date and result if data doesn't exist for that day.
例如,我有以下结果状态:SUCCESS和FAILED.如果在21日没有失败的结果,我希望返回的计数为0:
For example I have the following result statuses: SUCCESS and FAILED. If on the 21st there were no results of FAILED I would want a count returned of 0:
{
"_id" : {
"month" : 1,
"day" : 21,
"year" : 2014,
"buildResult" : "FAILURE"
},
"count" : 0
}
我已经对关系数据库和日历表做了类似的事情,但是我不确定如何使用MongoDB来解决这个问题.这可能吗?还是应该在运行查询后以编程方式执行某些操作?
I've done something similar with a relational database and a calendar table, but I'm not sure how to approach this with MongoDB. Is this possible or should I do something programatically after running the query?
以下是数据库中文档的示例(简体):
Here is an example of a document (simplified) in the database:
{
"_id" : ObjectId("52deab2fe4b0a491abb54108"),
"type" : "build",
"time" : ISODate("2014-01-21T17:15:27.471Z"),
"data" : {
"buildNumber" : 43,
"buildDuration" : 997308,
"buildResult" : "SUCCESS"
}
}
这是我当前的查询:
db.builds.aggregate([
{ $match: { "data.buildResult" : { $ne : null} }},
{ $group: {
_id: {
month: { $month: "$time" },
day: { $dayOfMonth: "$time" },
year: { $year: "$time" },
buildResult: "$data.buildResult",
},
count: { $sum: 1}
} },
{ $sort: { "_id.year": 1, "_id.month": 1, "_id.day": 1} }
])
推荐答案
如果我正确理解了您想要什么,则可以尝试以下方法:
If I correctly understand what do you want, you could try this:
db.builds.aggregate([
{ $project:
{
time: 1,
projectedData: { $ifNull: ['$data.buildResult', 'none'] }
}
},
{ $group: {
_id: {
month: { $month: "$time" },
day: { $dayOfMonth: "$time" },
year: { $year: "$time" },
buildResult: "$projectedData"
},
count: { $sum: { $cond: [ { $eq: [ "$projectedData", "none" ] }, 0, 1 ] } }
} },
{ $sort: { "_id.year": 1, "_id.month": 1, "_id.day": 1 } }
])
更新:
您要从输出中获取输入中的更多文档,只能使用与数组一起使用的unwind
运算符,但是您没有任何数组,因此据我所知,您无法获取更多文档.因此,您应该在查询结果之后添加一些逻辑,以便为另一种buildResult类型的现有日期创建新数据(计数为0)
Update:
You want to get from output more documents that been in input, it is possible only with unwind
operator that works with arrays, but you haven't any arrays, so as I know it is impossible to get more documents in your case. So you should add some logic after query result to create new data for existing dates with 0 count for another type of buildResult...
这篇关于如果没有结果,则MongoDB的总返回计数为0的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!