Mongo aggregation to filter documents based on the max of a field

user_1805265 0
Viewed 182

The collection has fields like key,version,date and status. Same key can have multiple entries in the collection with a unique version, date and Status.

How can we write an aggregation to find all the documents with max version. For example - I have a sample collection created here - https://mongoplayground.net/p/nyAdYmzf59H

The expected output is

[{
        "key": 1,
        "version": 2,
        "date": "Feb/10",
        "status": "DRAFT"
    }, {
        "key": 2,
        "version": 1,
        "date": "March/10",
        "status": "ACTIVE"
    }, {
        "key": 3,
        "version": 3,
        "date": "Jun/10",
        "status": "DRAFT"
    }
]
aggregation-framework mongodb-query mongodb
1 Answers

Demo - https://mongoplayground.net/p/WyKH2fVbWfA

db.collection.aggregate([
  { "$sort": { "version": -1 } }, // sort descending by version 
  {
    "$group": {
      "_id": "$key", // group by key
      "version": { "$first": "$version" }, // pick top version which will be max here
      "date": { "$first": "$date" },
      "status": { "$first": "$status"}
    }
  },
  { $project: { _id: 0,  key: "$_id", version: 1, date: 1, status: 1 }}
])

Demo - https://mongoplayground.net/p/e1Bw7rVGd0Q

db.collection.aggregate([
  { $sort: { "version": -1 } },
  { $group: { "_id": "$key", "doc": { "$first": "$$ROOT" } } },
  { $replaceRoot: { "newRoot": "$doc" } },
  { $project: { _id: 0 } }
])
user_22242650
Related