MongoDB - Get id of the maximum value in a group by query

Question

I have a collection where documents look like below. I would like to get the "_id" or the entire document of those with maximum revision in each group grouped by "name".

{
    "_id" : ObjectId("55aa088a58f2a8f2bd2a3793"),
    "name" : "abc",
    "revision" : 1
}

{
    "_id" : ObjectId("55aa088a58f2a8f2bd2a3794"),
    "name" : "abc",
    "revision" : 2
}

{
    "_id" : ObjectId("55aa088a58f2a8f2bd2a3795"),
    "name" : "def",
    "revision" : 1
}

{
    "_id" : ObjectId("55aa088a58f2a8f2bd2a3796"),
    "name" : "def",
    "revision" : 2
}

In this case, I'd like to select only 2nd and 4th documents. I tried various approach including aggregation framework but couldn't do it purely in MongoDB query.

Is there a solution for this?

Thank you.

Answer 1

Try the following aggregation pipeline which first sorts the documents by the revision field descending and then $group by the name field, add another field that gets the full document by using the $first operator on the $$ROOT system variable expression:

db.collection.aggregate([
    {
        "$sort": {
            "revision": -1
        }
    },
    {
        "$group": {
            "_id": "$name",
            "max_revision": {
                "$max": "$revision"
            },
            "doc": {
                "$first": "$$ROOT"
            }
        }
    }
])

Output:

/* 0 */
{
    "result" : [ 
        {
            "_id" : "def",
            "max_revision" : 2,
            "doc" : {
                "_id" : ObjectId("55aa088a58f2a8f2bd2a3796"),
                "name" : "def",
                "revision" : 2
            }
        }, 
        {
            "_id" : "abc",
            "max_revision" : 2,
            "doc" : {
                "_id" : ObjectId("55aa088a58f2a8f2bd2a3794"),
                "name" : "abc",
                "revision" : 2
            }
        }
    ],
    "ok" : 1
}

Answer 2

You should use Mongo aggregation to get expected output.

The query will be like following:

db.collection.aggregate({
    $group: {
    _id: "$name",
    "revision": {
        $max: "$revision"
    }
    }
}, {
    $project: {
    "name": "$_id",
    "revision": 1,
    "_id": 0
    }
})

EDIT

You can use query like following-

db.collection.aggregate({
    $sort: {
    revision: -1
    }
}, {
    $group: {
    _id: "$name",
    "revision": {
        "$max": "$revision"
    },
    "original": {
        $first: "$$ROOT"
    }
    }
}, {
    $project: {
    "name": "$_id",
    "revision": 1,
    "_id": "$original._id"
    }
})