在mongoDB聚合查询中需要帮助

赛库玛尔A

我需要每个日期的成功状态的最大日期。以下是文档结构。我尝试使用聚合但无法获得所需的结果。

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

克列丹

使用以下聚合管道：

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

样本输出：

/* 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
}

-编辑-

要包括状态SUCCESS和FAILED每个状态的计数mid，您需要更改$match查询以使用$in具有这两种状态的运算符，$groupby标识符也将更改，以按mid和status字段对文档进行分组，然后可以添加计数通过将累加器运算符$sum应用于组：

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

样本输出

/* 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
}