Need help in mongoDB aggregate Query

Question

I need max date for SUCCESS status for each mid .Below is the document structure. I tried using aggregate but unble to get the required result.

{"mid":1,"date":ISODate("2015-06-07T00:00:00Z"),"status":"FAILED"}
{"mid":1,"date":ISODate("2015-06-04T00:00:00Z"),"status":"FAILED"}
{"mid":1,"date":ISODate("2015-06-01T00:00:00Z"),"status":"SUCCESS"}
{"mid":1,"date":ISODate("2015-05-27T00:00:00Z"),"status":"SUCCESS"}
{"mid":2,"date":ISODate("2015-05-17T00:00:00Z"),"status":"SUCCESS"}
{"mid":2,"date":ISODate("2015-05-07T00:00:00Z"),"status":"FAILED"}
{"mid":3,"date":ISODate("2015-05-04T00:00:00Z"),"status":"FAILED"}
{"mid":3,"date":ISODate("2015-05-03T00:00:00Z"),"status":"FAILED"}
{"mid":3,"date":ISODate("2015-05-02T00:00:00Z"),"status":"SUCCESS"}
{"mid":3,"date":ISODate("2015-05-01T00:00:00Z"),"status":"FAILED"}
{"mid":4,"date":ISODate("2015-04-07T00:00:00Z"),"status":"FAILED"}
{"mid":4,"date":ISODate("2015-04-01T00:00:00Z"),"status":"SUCCESS"}

Answer 1

Use the following aggregation pipeline:

db.collection.aggregate([
    {
        "$match": {"status": "SUCCESS"}
    },
    {
        "$group": {
            "_id": "$mid",
            "maxDate": {
                "$max": "$date"
            }
        }
    }
])

Sample Output:

/* 0 */
{
    "result" : [ 
        {
            "_id" : 4,
            "maxDate" : ISODate("2015-04-01T00:00:00.000Z")
        }, 
        {
            "_id" : 3,
            "maxDate" : ISODate("2015-05-02T00:00:00.000Z")
        }, 
        {
            "_id" : 2,
            "maxDate" : ISODate("2015-05-17T00:00:00.000Z")
        }, 
        {
            "_id" : 1,
            "maxDate" : ISODate("2015-06-01T00:00:00.000Z")
        }
    ],
    "ok" : 1
}

---

-- EDIT --

To include the counts for the status SUCCESS and FAILED for each mid, you need to change the $match query to use an $in operator with those two statuses, the $group by identifiers would change as well to group the documents by the mid and status fields and you can then add the count by applying the accumulator operator $sum on the group:

db.collection.aggregate([
    {
        "$match": {
            "status": { "$in": ["SUCCESS", "FAILED"] }
        }
    },
    {
        "$group": {
            "_id": {
                "mid": "$mid",
                "status": "$status"
            },
            "maxDate": {
                "$max": "$date"
            },
            "count": {
                "$sum": 1
            }
        }
    }
])

Sample Output

/* 0 */
{
    "result" : [ 
        {
            "_id" : {
                "mid" : 4,
                "status" : "SUCCESS"
            },
            "maxDate" : ISODate("2015-04-01T00:00:00.000Z"),
            "count" : 1
        }, 
        {
            "_id" : {
                "mid" : 3,
                "status" : "FAILED"
            },
            "maxDate" : ISODate("2015-05-04T00:00:00.000Z"),
            "count" : 3
        }, 
        {
            "_id" : {
                "mid" : 3,
                "status" : "SUCCESS"
            },
            "maxDate" : ISODate("2015-05-02T00:00:00.000Z"),
            "count" : 1
        }, 
        {
            "_id" : {
                "mid" : 2,
                "status" : "FAILED"
            },
            "maxDate" : ISODate("2015-05-07T00:00:00.000Z"),
            "count" : 1
        }, 
        {
            "_id" : {
                "mid" : 4,
                "status" : "FAILED"
            },
            "maxDate" : ISODate("2015-04-07T00:00:00.000Z"),
            "count" : 1
        }, 
        {
            "_id" : {
                "mid" : 2,
                "status" : "SUCCESS"
            },
            "maxDate" : ISODate("2015-05-17T00:00:00.000Z"),
            "count" : 1
        }, 
        {
            "_id" : {
                "mid" : 1,
                "status" : "SUCCESS"
            },
            "maxDate" : ISODate("2015-06-01T00:00:00.000Z"),
            "count" : 2
        }, 
        {
            "_id" : {
                "mid" : 1,
                "status" : "FAILED"
            },
            "maxDate" : ISODate("2015-06-07T00:00:00.000Z"),
            "count" : 2
        }
    ],
    "ok" : 1
}