Get the most recent object for each category in MongoDB-CodePudding

I have a collection that looks like this

{
  category: "Groceries",
  items: [...],
  price: 30.0,
  purchased: "04-10-2022 17:52:35"
},
{
  category: "Groceries",
  items: [...],
  price: 11.0,
  purchased: "04-10-2022 18:52:35"
},
{
  category: "Cleaners",
  items: [...],
  price: 10.0,
  purchased: "04-10-2022 17:52:35"
},

I want to take the most recent purchase for each category with one single query.

What the results should be are:

{
  category: "Groceries",
  items: [...],
  price: 11.0,
  purchased: "04-10-2022 18:52:35"
},
{
  category: "Cleaners",
  items: [...],
  price: 10.0,
  purchased: "04-10-2022 17:52:35"
},

CodePudding user response：

First you need to use sort to order all records purchased property
Second group by the category and only push the first item.
replace root to get the main record body.

[
  // sort the dates to get the latest first
  {$sort: {purchased: -1}},
  // group to get the most recent purchased record per category
  {$group: {_id: '$category', record: {$first: '$$ROOT'}}},
  // replace root to get the main record body
  {$replaceRoot: {newRoot: '$record'}},
]