I have this document structure:

{
 ...,
insertionDate: 'someDate',
product: 'cool jacket',
color: 'red',
store: 'north store',
}

The thing is this collection stores every change a product has ever had. At this moment I have the following code to retrieve all the products in one store with one color of a set previously defined.

const colors = ['red', 'blue', 'orange'];    
collection.find({$and: [{store: store)}, {color: {$in: colors}}]});

The problem is that I get all the historical data, for example I can get 'cool jacket' in each color 4 times, because it appears multiple times in the database with different insertionDate.

How do I get the last insertionDate of each product?

CodePudding user response：

You can use collection.find({$and: [{store: store)}, {color: {$in: colors}}]}).sort(insertionDate: -1) will sort them according to the last inserted date.

CodePudding user response：

You can achieve this using aggregate query with three pipelines.

1. $match to match the desired store and colors.

2. Use $sort to sort by insertionDate desc order.

3. $group to group by product Name or product Id. While grouping $first accumulator will help to get the recent inserted product.

CodePudding user response：

Here is a tested code:

await Collection.aggregate([
    {
      $match: {
        color: { $in: colors }
      }
    },
    { $sort: { insersionDate: -1 } },
    { $group: { _id: '$product', doc: { $first: '$$ROOT' } } }
  ]);