在 mongoDB 聚合查询中需要帮助

Question

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

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

使用以下聚合管道：

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
}

---

--

编辑 --

要包括每个mid的状态SUCCESS和FAILED计数，您需要更改$match查询以使用具有这两种状态的$in运算符，$group标识符也会更改以按中间和状态字段对文档进行分组，然后您可以通过在组上应用累加器运算符$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
}