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.

Yong Shun · Accepted Answer · 2022-02-21 05:12:02Z

1

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

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

Sample Mongo Playground

answered Feb 21, 2022 at 5:12

Yong Shun

54.5k6 gold badges38 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Saravanan S Over a year ago

Awesome. Works as expected! Thanks a lot

Collectives™ on Stack Overflow

MongoDB query: Find multiple first matching document

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest