How to retrieve documents where a field is maximum for every other distinct field on MongoDB?-CodePudding

I have the following collection on MongoDB (I've omitted the field _id for clarity):

[
  {
    "stock": "Stock1",
    "value": 12,
    "month": 1,
    "year": 2019
  },
  {
    "stock": "Stock1",
    "value": 13,
    "month": 2,
    "year": 2019
  },
  {
    "stock": "Stock1",
    "value": 14,
    "month": 1,
    "year": 2020
  },
  {
    "stock": "Stock1",
    "value": 15,
    "month": 2,
    "year": 2020
  },
  {
    "stock": "Stock2",
    "value": 6,
    "month": 1,
    "year": 2019
  },
  {
    "stock": "Stock2",
    "value": 5,
    "month": 2,
    "year": 2019
  }
]

And I wish to retrieve the documents for each stock with the latest (maximum) year available, such that I end up with the following:

[
  {
    "stock": "Stock1",
    "value": 14,
    "month": 1,
    "year": 2020
  },
  {
    "stock": "Stock1",
    "value": 15,
    "month": 2,
    "year": 2020
  },
  {
    "stock": "Stock2",
    "value": 6,
    "month": 1,
    "year": 2019
  },
  {
    "stock": "Stock2",
    "value": 5,
    "month": 2,
    "year": 2019
  }
]

I'm tempted to programmatically loop over each stock and in a first step query the last year with find({"stock": "Stockx"}).sort({"year": -1}).limit(1) and in a second step query find({"stock": "Stockx", "year": maxyearforStockx"}). Is there a more elegant way of doing this with the find or aggregate commands?

Any help would be greatly appreciated!

CodePudding user response：

$sort - Sort by year descending.
$group - Group by stock and year.
$group - Group by stock and take the first item of stockYearItems (from 2).
$unwind - Descontruct items.stockYearItems array to multiple documents.
$replaceRoot - Replace the input document with new document to show stock documents.

db.collection.aggregate({
  "$sort": {
    "year": -1
  }
},
{
  $group: {
    _id: {
      "stock": "$stock",
      "year": "$year"
    },
    stockYearItems: {
      $push: "$$ROOT"
    }
  }
},
{
  $group: {
    _id: "$_id.stock",
    items: {
      $first: "$$ROOT"
    }
  }
},
{
  $unwind: "$items.stockYearItems"
},
{
  "$replaceRoot": {
    "newRoot": "$items.stockYearItems"
  }
})

Sample Mongo Playground