MongoDB query: Find multiple first matching document

Question

I have a collection with the following schema:

{
  _id: ObjectId,
  company: ObjectId,
  endTime: date string
}

There will be multiple documents for the same company. Few example docs:

{_id: 1, company: companyA, endTime: Jan 1,2022}
{_id: 2, company: companyB, endTime: Feb 11,2022}
{_id: 3, company: companyA, endTime: Jan 3,2022}
{_id: 4, company: companyB, endTime: Jan 4,2022}
{_id: 5, company: companyA, endTime: Feb 21,2022}
{_id: 5, company: companyB, endTime: Jan 1,2022}

Now I want to get the first document per company sorted by endTime (desc). I tried with aggregate but was not able to accomplish it.

Result for above docs:

{_id: 5, company: companyA, endTime: Feb 21,2022}
{_id: 2, company: companyB, endTime: Feb 11,2022}

Any help would be appreciated.

Answer 1

1. $sort - Sort by endTime DESC.
2. $group - Group by company and take first document ($first) as data field.
3. $replaceWith - Replace the input document with data.

db.collection.aggregate([
  {
    $sort: {
      endTime: -1
    }
  },
  {
    $group: {
      _id: "$company",
      data: {
        $first: "$$ROOT"
      }
    }
  },
  {
    "$replaceWith": "$data"
  }
])

Sample Mongo Playground