Assume I have a collection that looks something like this:

[{
    _id: new ObjectId(),
    type: 'foo',
    value: 123,
    date: '2022-06-30',
}, {
    _id: new ObjectId(),
    type: 'bar',
    value: 321,
    date: '2022-06-29',
}, {
    _id: new ObjectId(),
    type: 'foo',
    value: 456,
    date: '2022-06-28',
}, {
    _id: new ObjectId(),
    type: 'bar',
    value: 789,
    date: '2022-06-27',
}, {
    _id: new ObjectId(),
    type: 'baz',
    value: 234,
    date: '2022-06-26',
},
// etc....
]

It's sorted by date.

I want to get all of the top items of the first 2 types as they appear. In this case, that means I want to get everything above the last item shown, which is of type "baz", the third type to exist after type "foo" and "bar".

// Expected result
[{
    _id: new ObjectId(),
    type: 'foo',
    value: 123,
    date: '2022-06-30',
}, {
    _id: new ObjectId(),
    type: 'bar',
    value: 321,
    date: '2022-06-29',
}, {
    _id: new ObjectId(),
    type: 'foo',
    value: 456,
    date: '2022-06-28',
}, {
    _id: new ObjectId(),
    type: 'bar',
    value: 789,
    date: '2022-06-27',
}]

Assuming this new item is added to the collection:

{
    _id: new ObjectId(),
    type: 'baz',
    value: 567,
    date: '2022-07-01',
}

The new expected result would be

[{
    _id: new ObjectId(),
    type: 'baz',
    value: 567,
    date: '2022-07-01',
}, {
    _id: new ObjectId(),
    type: 'foo',
    value: 123,
    date: '2022-06-30',
}]

...as "bar" from 2022-06-29 would now be the third type to appear in the ordered set.

CodePudding user response：

Since mongoDB version 5.0 you can use $setWindowFields for this:

1. The $setWindowFields allows to sort according to the date and add a new field to each document, with a set of the types "so far".
2. Now we just need to match only documents with less than 3 topTypes.

db.collection.aggregate([
  {
    $setWindowFields: {
      sortBy: {date: -1},
      output: {
        topTypes: {
          $addToSet: "$type",
          window: {documents: ["unbounded", "current"]}
        }
      }
    }
  },
  {
    $match: {$expr: {$lt: [{$size: "$topTypes"}, 3]}}
  },
  {
    $unset: "topTypes"
  }
])

See how it works on the playground example