汇总符合条件和分组的子文档数 [英] Aggregate with count of sub documents matching the condition and grouping

问题描述

我收集了如下文件:

{
    "_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" : "PASS"
        },
        {
            "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": 2,
        "suite-fail": 0,
        "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.

我按照布雷克(Blakes)在

I tried this as suggested by Blakes in Mongodb: Group by element and show the sub-document count based on condition and sort the document by date:

db.testruns.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 } }
    ]);

我期望Project2的suite-pass计数为2，因为suiteList中有2个元素，但它返回1.

I was expecting the suite-pass count for Project2 as 2 since there are 2 elements in suiteList, but it returns 1.

推荐答案

您应该已经正确阅读了答案，因为已经有另一个替代清单，并解释了为什么您想要的预期结果与您使用的预期结果会有所不同.

You should have read the answer properly, as there already was another alternate listing and explanation of why the expected result you want from the one you used would be different.

相反，您想要这个，它尊重可能的多个"PASS"或"FAIL":

Instead you want this one, which respects the possible multiple "PASS" or "FAIL":

  Model.aggregate(
    [
      { "$sort": { "executionProject": 1, "runEndTime": 1 } },
      { "$group": {
        "_id": "$executionProject",
        "suiteList": { "$last": "$suiteList" },
        "runEndTime": { "$last": "$runEndTime" }
      }},
      { "$unwind": "$suiteList" },
      { "$group": {
        "_id": "$_id",
        "suite-pass": { 
          "$sum": {
            "$cond": [
              { "$eq": [ "$suiteList.suiteStatus", "PASS" ] },
              1,
              0
            ]
          }
        },
        "suite-fail": { 
          "$sum": {
            "$cond": [
              { "$eq": [ "$suiteList.suiteStatus", "FAIL" ] },
              1,
              0
            ]
          }
        },
        "runEndTime": {"$first": "$runEndTime"}
      }},
      { "$sort": { "runEndTime": 1 }}
    ],
    function(err,result) {

    }
  );

这是方法的组合".首先是按照您的期望在运行时获得最后".接下来是分解数组，这一次实际上是总结"通过或失败的可能发生，而不是仅记录数组中通过或失败的1，即实际的通过"或失败"被计数.

Which is sort of a "combination" of approaches. The first is to get the "last" by runTime as you were expecting. The next is to break down the array and this time actually "sum up" the possible occurances of pass or fail, rather than just record a 1 for either pass or fail in the array, the actual "pass" or "fail" are counted.

有结果:

{
        "_id" : "Project1",
        "suite-pass" : 0,
        "suite-fail" : 1,
        "runEndTime" : ISODate("2015-08-19T09:46:31.108Z")
}
{
        "_id" : "Project2",
        "suite-pass" : 2,
        "suite-fail" : 0,
        "runEndTime" : ISODate("2015-08-19T11:09:52.537Z")
}
{
        "_id" : "Project3",
        "suite-pass" : 0,
        "suite-fail" : 1,
        "runEndTime" : ISODate("2015-08-19T11:18:41.460Z")
}

这篇关于汇总符合条件和分组的子文档数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！