Mongodb:按元素分组并根据条件显示子文档计数，并按日期对文档进行排序 [英] Mongodb: Group by element and show the sub-document count based on condition and sort the document by date
问题描述
我收集了如下文件:
{
"_id" : ObjectId("55d4410544c96d6f6578f893"),
"executionProject" : "Project1",
"suiteList" : [
{
"suiteStatus" : "PASS",
}
],
"runEndTime" : ISODate("2015-08-19T08:40:47.049Z"),
"runStartTime" : ISODate("2015-08-19T08:40:37.621Z"),
"runStatus" : "PASS",
"__v" : 1
}
{
"_id" : ObjectId("55d44eb4c0422e7b8bffe76b"),
"executionProject" : "Project1",
"suiteList" : [
{
"suiteStatus" : "PASS",
}
],
"runEndTime" : ISODate("2015-08-19T09:39:13.528Z"),
"runStartTime" : ISODate("2015-08-19T09:39:00.406Z"),
"runStatus" : "PASS",
"__v" : 1
}
{
"_id" : ObjectId("55d44f0bc0422e7b8bffe76f"),
"executionProject" : "Project1",
"suiteList" : [
{
"suiteStatus" : "FAIL",
}
],
"runEndTime" : ISODate("2015-08-19T09:46:31.108Z"),
"runStartTime" : ISODate("2015-08-19T09:40:27.377Z"),
"runStatus" : "PASS",
"__v" : 1
}
{
"_id" : ObjectId("55d463d0c0422e7b8bffe789"),
"executionProject" : "Project2",
"suiteList" : [
{
"suiteStatus" : "FAIL"
},
{
"suiteStatus" : "PASS"
}
],
"runEndTime" : ISODate("2015-08-19T11:09:52.537Z"),
"runStartTime" : ISODate("2015-08-19T11:09:04.539Z"),
"runStatus" : "FAIL",
"__v" : 1
}
{
"_id" : ObjectId("55d464ebc0422e7b8bffe7c2"),
"executionProject" : "Project3",
"suiteList" : [
{
"suiteStatus" : "FAIL"
}
],
"runEndTime" : ISODate("2015-08-19T11:18:41.460Z"),
"runStartTime" : ISODate("2015-08-19T11:13:47.268Z"),
"runStatus" : "FAIL",
"__v" : 10
}
我期望输出如下:
[
{
"executionProject": "Project1",
"suite-pass": 0,
"suite-fail": 1,
"runEndTime": ISODate("2015-08-19T09:46:31.108Z")
},
{
"executionProject": "Project2",
"suite-pass": 1,
"suite-fail": 1,
"runEndTime": ISODate("2015-08-19T11:09:52.537Z")
},
{
"executionProject": "Project3",
"suite-pass": 0,
"suite-fail": 1,
"runEndTime": ISODate("2015-08-19T11:18:41.460Z")
},
]
我想按项目分组，并按runEndTime排序，并显示suiteList的通过和失败计数.
I want to group by project and order by runEndTime and show the pass and fail counts of suiteList.
我知道如何获得套件通过和所有运行的失败计数，但是不确定如何分组和订购.请帮助.
I know how to get the suite pass and fail counts of all runs, but not sure how can I group and order. Kindly help.
推荐答案
具有 $cond 的聚合框架运算符似乎就是您想要的:
The aggregation framework with the $cond operator seems to be what you are after:
Model.aggregate([
{ "$unwind": "$suiteList" },
{ "$group": {
"_id": "$executionProject",
"suite-pass": {
"$sum": {
"$cond": [
{ "$eq": [ "$suiteList.suiteStatus", "PASS" ] },
1,
0
]
}
},
"suite-fail": {
"$sum": {
"$cond": [
{ "$eq": [ "$suiteList.suiteStatus", "FAIL" ] },
1,
0
]
}
},
"runEndTime": { "$max": "$runEndTime" }
}},
{ "$sort": { "runEndTime": 1 }}
],function(err,result) {
});
这将有条件地测试通过"或失败"的值，并将它们返回给 $sum 累加器> $group .您以相同的方式使用 $max 每个分组文档相关日期的值.
This conditionally tests the values for either "PASS" or "FAIL" and returns these to the $sum accumulator under $group. In the same way you take the $max value for the relevant date per grouped documents.
最后一件事就是 $sort > 在该日期之前
The final thing is just to $sort by that date
如果"suiteList"实际上最多只能包含一个"PASS"和一个"FAIL"，那么您可能甚至不用
If the "suiteList" in fact will only ever ccontain one "PASS" and one "FAIL" at maximum, then you can likely get away without even using $unwind on the array:
Model.aggregate(
[
{ "$group": {
"_id": "$executionProject",
"suite-pass": {
"$sum": {
"$cond": [
{ "$anyElementTrue": {
"$map": {
"input": "$suiteList",
"as": "suite",
"in": {
"$eq": [ "$$suite.suiteStatus", "PASS" ]
}
}
}},
1,
0
]
}
},
"suite-fail": {
"$sum": {
"$cond": [
{ "$anyElementTrue": {
"$map": {
"input": "$suiteList",
"as": "suite",
"in": {
"$eq": [ "$$suite.suiteStatus", "FAIL" ]
}
}
}},
1,
0
]
}
},
"runEndTime": { "$max": "$runEndTime" }
}},
{ "$sort": { "runEndTime": 1 }}
],
function(err,results) {
}
)
$map 和 $anyElementTrue 在其中可以类似地测试匹配条件中应该对它们进行计数的条件.因此，只要每个文档的匹配数"都没有关系，就可以了.
The $map and $anyElementTrue in there can similarly test the the conditions in the array matched where they should be counted. So as long as the "number of matches" per document does not matter, then this is fine.
在这里，您的数据确实满足这些条件，然后两者都产生相同的结果:
On your data here that does meet those conditions, then both produce the same result:
{
"_id" : "Project1",
"suite-pass" : 2,
"suite-fail" : 1,
"runEndTime" : ISODate("2015-08-19T09:46:31.108Z")
}
{
"_id" : "Project2",
"suite-pass" : 1,
"suite-fail" : 1,
"runEndTime" : ISODate("2015-08-19T11:09:52.537Z")
}
{
"_id" : "Project3",
"suite-pass" : 0,
"suite-fail" : 1,
"runEndTime" : ISODate("2015-08-19T11:18:41.460Z")
}
To get the "last" items then simply $sort first and replace the $sum accumlators with the $last accumulator:
Model.aggregate(
[
{ "$sort": { "runEndTime": 1 } },
{ "$group": {
"_id": "$executionProject",
"suite-pass": {
"$last": {
"$cond": [
{ "$anyElementTrue": {
"$map": {
"input": "$suiteList",
"as": "suite",
"in": {
"$eq": [ "$$suite.suiteStatus", "PASS" ]
}
}
}},
1,
0
]
}
},
"suite-fail": {
"$last": {
"$cond": [
{ "$anyElementTrue": {
"$map": {
"input": "$suiteList",
"as": "suite",
"in": {
"$eq": [ "$$suite.suiteStatus", "FAIL" ]
}
}
}},
1,
0
]
}
},
"runEndTime": { "$last": "$runEndTime" }
}},
{ "$sort": { "runEndTime": 1 } }
],
function(err,results) {
}
);
哪个会产生:
{
"_id" : "Project1",
"suite-pass" : 0,
"suite-fail" : 1,
"runEndTime" : ISODate("2015-08-19T09:46:31.108Z")
}
{
"_id" : "Project2",
"suite-pass" : 1,
"suite-fail" : 1,
"runEndTime" : ISODate("2015-08-19T11:09:52.537Z")
}
{
"_id" : "Project3",
"suite-pass" : 0,
"suite-fail" : 1,
"runEndTime" : ISODate("2015-08-19T11:18:41.460Z")
}
这篇关于Mongodb:按元素分组并根据条件显示子文档计数，并按日期对文档进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
