Context:

I have a MongoDB full of Documents like this:

[
  {
    "_id": "615dc97907f597330c510279",
    "code": "SDFSDFSDF",
    "location": "ABC1",
    "week_number": 39,
    "year": 2020,
    "region": "NA"
  },

  ....

  {
    "_id": "615dc97907f597330c51027a",
    "code": "SDFSGSGR",
    "location": "ABC1",
    "week_number": 42,
    "year": 2021,
    "region": "EU"
  },

  ....

  {
    "_id": "615dc97607f597330c50ff50",
    "code": "GGSFHSFS",
    "location": "DEF2",
    "week_number": 42,
    "year": 2021,
    "region": "EU",
    "audit_result": {
      "issues_found": true,
      "comment": "comment."
    }
  }
]

Problem

I am trying to write an aggregation which should return all object with the highest "week_number" and highest "year". So with the example above, I want to return the full documents of _id "615dc97907f597330c51027a" and "615dc97607f597330c50ff50".

I tried multiple approaches. like first sorting and then grouping but with no success. currently I have something like this, which seemed logical, but it returns all documents not only the one with the highest week and year

[
    {
        '$match': {
            '$expr': {
                '$max': '$week_number', 
                '$max': '$year'
            }
        }
    }
]

CodePudding user response：

You can do the followings in an aggregation pipeline:

1. $group by year and week_number; push the _id into an array for future lookup
2. $sort by year: -1 and week_number: -1
3. $limit to get the first grouping, which is the one with max year and week_number
4. $lookup the original documents using the previously stored array of _id in step 1
5. $replaceRoot to get back the documents

Here is the Mongo playground for your reference.