Aggregate with count of sub documents matching the condition and grouping

Question

I've collections of documents as like as below:

{
    "_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
}

And I'm expecting output as follows:

[
    {
        "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")
    },
]

I want to group by project and order by runEndTime and show the pass and fail counts of suiteList.

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

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

Blakes Seven · Accepted Answer · 2015-08-25 07:46:03Z

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.

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) {

    }
  );

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.

With results:

{
        "_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")
}

You are a genius man. I would like to offer you a beer if you are around here.

Neo-coder · Accepted Answer · 2015-08-25 07:37:40Z

Unwind suiteList and used $sum in group as below :

db.testruns.aggregate({
    "$unwind": "$suiteList"
}, {
    "$group": {
        "_id": "$executionProject",
        "suite-pass": {
            "$sum": {
                "$cond": {
                    "if": {
                        "$eq": ["$suiteList.suiteStatus", "PASS"]
                    },
                    "then": 1,
                    "else": 0
                }
            }
        },
        "suite-fail": {
            "$sum": {
                "$cond": {
                    "if": {
                        "$eq": ["$suiteList.suiteStatus", "FAIL"]
                    },
                    "then": 1,
                    "else": 0
                }
            }
        },
        "runEndTime": {
            "$last": "$runEndTime"
        }
    }
}, {
    "$sort": {
        "runEndTime": 1
    }
})

This returns the suite-pass count as 2 and suite-fail count as 1 for Project1. I was expecting the suite-pass count as 0 and suite-fail count as 1.
@Vimal You do realize this is a direct copy paste from the answer I gave you originally before you made it clear you wanted the "last" and not the "sum". Just so you know what you are rewarding.

Collectives™ on Stack Overflow

Aggregate with count of sub documents matching the condition and grouping

2 Answers 2

1 Comment

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Linked

Related