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

Question

If i have the following json structure:

[
    {
        id: 1,
        type: "Drinks",
        isActive : "true",
        location: "QLD"   
    },
    {
        id: 2,
        type: "Drinks",
        isActive : "false",
        location: "NSW"  
    },
    {
        id: 3,
        type: "Drinks",
        isActive : "true"
        location: "QLD"   
    },
    {
        id: 3,
        type: "Drinks",
        isActive : "false"
        location: "QLD"   
    },
    {
        id: 3,
        type: "Drinks",
        isActive : "true"
        location: "QLD"   
    },
    {
        id: 4,
        type: "Food",
        isActive : "true"
        location: "NSW"   
    },
    {
        id: 4,
        type: "Food",
        isActive : "false"
        location: "NSW"   
    }
]

The return i'm interested in is:

[
    {
        id: 1,
        type: "Drinks",
        isActive : "true",
        location: "QLD"   
    },
    {
        id: 2,
        type: "Drinks",
        isActive : "false",
        location: "NSW"  
    },
    {
        id: 3,
        type: "Drinks",
        isActive : "true",
        location: "QLD"    
    },
    {
        id: 4,
        type: "Food",
        isActive : "false",
        location: "NSW"    
    }
]

In other words, give me top 1 of each TYPE in each LOCATION sorted by ID descending. The records may be repeated as the sample data set looks so essentially i want all the unique types for each location. Is this something that can be done in mongoD?

It many not be relevant but i am using Mongoose within my nodejs app to interact with mongoDB.

Answer 1

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);
    });

Answer 2

Try the following query:-

db.collname.aggregate(
[
 {
   $group:
     {
       _id: "$type",
       id: { $max: "$id" }
     }
 }
]

Refer doc for more info.