Return records in mongoDB based on top 1 of field x grouped by type y

The aggregation framework is at your disposal to give you the desired results. You would have to run an aggregation pipeline consists of 3 stages, in the following order:

1. $sort

   • This pipe will allow you to first order the documents getting into the pipeline for grouping later. Sort the documents with the ID descending.

2. $group

   • The group pipeline operator is similar to the SQL's GROUP BY clause. In SQL, you can't use GROUP BY unless you use any of the aggregation functions. The same way, you have to use an aggregation function in MongoDB as well. In this instance, you need to group all the documents by the type, location and id keys, and use the required $first operator to bring in the first document (in other words, the TOP document when ordered).

3. $project

   • This pipeline step is similar to SELECT in SQL. Use this to rename the field names and select/deselect the fields to be returned, out of the grouped fields. If you specify 0 for a field, it will NOT be sent in the pipeline to the next operator.

Putting all the three pipes together, you can run the following aggregation pipeline to achieve the required result:

var pipeline = [
    { "$sort": { "id": -1 } },
    {
        "$group": {
            "_id": {
                "type": "$type", "location": "$location", "id": "$id"
            },            
            "isActive": { "$first": "$isActive" }
        }
    },
    {
        "$project": {
            "_id": 0,
            "id": "$_id.id",          
            "isActive": 1,
            "type": "$_id.type",
            "location": "$_id.location"
        }
    }
]

Model.aggregate(pipeline, function(err, result) {
    if err throw err;
    console.log(result);
});

Or using the fluent API

Model.aggregate()
    .sort("-id")
    .group({
        "_id": {
            "type": "$type", "location": "$location", "id": "$id"
        },            
        "isActive": { "$first": "$isActive" }
    })
    .project({
        "_id": 0,
        "id": "$_id.id",          
        "isActive": 1,
        "type": "$_id.type",
        "location": "$_id.location"
    })
    .exec(unction(err, result) {
        if err throw err;
        console.log(result);
    });