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?
Here is a tested code:
await Collection.aggregate([
{
$match: {
color: { $in: colors }
}
},
{ $sort: { insersionDate: -1 } },
{ $group: { _id: '$product', doc: { $first: '$$ROOT' } } }
]);
You can achieve this using aggregate query with three pipelines.
$match to match the desired store and colors.
Use $sort to sort by insertionDate desc order.
$group to group by product Name or product Id. While grouping $first accumulator will help to get the recent inserted product.
You can use collection.find({$and: [{store: store)}, {color: {$in: colors}}]}).sort(insertionDate: -1) will sort them according to the last inserted date.