/* 1 */
{
"_id" : ObjectId("5f0b6699486ea60a2a9c62fh"),
"EventDatetime" : "2020-07-12T19:38:00.653",
"Eventstatus" : "05",
"DelNumber" : "0703676929",
"DELIVERY_ITEM" : [
{
"Product_Code" : "123",
"Product_Name" : "utensils",
"Shipment_Quantity" : "12",
},
{
"Product_Code" : "456",
"Product_Name" : "clothes",
"Shipment_Quantity" : "10",
}
]
}
{
"_id" : ObjectId("5f0b6699486ea60a2a9c62fi"),
"EventDatetime" : "2020-07-12T19:38:00.653",
"Eventstatus" : "03",
"DelNumber" : "0703676929",,
"DELIVERY_ITEM" : [
{
"Product_Code" : "123",
"Product_Name" : "utensils",
"Shipment_Quantity" : "12",
},
{
"Product_Code" : "456",
"Product_Name" : "clothes",
"Shipment_Quantity" : "10",
}
]
}
{
"_id" : ObjectId("5f0b6699486ea60a2a9c62fg"),
"EventDatetime" : "2020-07-12T19:38:00.653",
"Eventstatus" : "02",
"DelNumber" : "0703676929",
"DELIVERY_ITEM" : [
{
"Product_Code" : "123",
"Product_Name" : "utensils",
"Shipment_Quantity" : "12",
},
{
"Product_Code" : "456",
"Product_Name" : "clothes",
"Shipment_Quantity" : "10",
}
]
}
/*2*/
{
"_id" : ObjectId("5f0b6699486ea60a2a9c62fa"),
"EventDatetime" : "2020-07-12T19:38:00.653",
"Eventstatus" : "04",
"DelNumber" : "0703676928",
"DELIVERY_ITEM" : [
{
"Product_Code" : "123",
"Product_Name" : "utensils",
"Shipment_Quantity" : "12",
},
{
"Product_Code" : "456",
"Product_Name" : "clothes",
"Shipment_Quantity" : "10",
}
]
}
{
"_id" : ObjectId("5f0b6699486ea60a2a9c62fb"),
"EventDatetime" : "2020-07-12T19:38:00.653",
"Eventstatus" : "02",
"DelNumber" : "0703676928",
"DELIVERY_ITEM" : [
{
"Product_Code" : "123",
"Product_Name" : "utensils",
"Shipment_Quantity" : "12",
},
{
"Product_Code" : "456",
"Product_Name" : "clothes",
"Shipment_Quantity" : "10",
}
]
}
{
"_id" : ObjectId("5f0b6699486ea60a2a9c62fc"),
"EventDatetime" : "2020-07-12T19:38:00.653",
"Eventstatus" : "01",
"DelNumber" : "0703676928",
"DELIVERY_ITEM" : [
{
"Product_Code" : "123",
"Product_Name" : "utensils",
"Shipment_Quantity" : "12",
},
{
"Product_Code" : "456",
"Product_Name" : "clothes",
"Shipment_Quantity" : "10",
}
]
}
I am having a collection for orders. Consider it like a ecommerce site. Now, order can have multiple status 00,01,02,03,04 and 05 . 05 refers to live tracking of the order . So my delno.1 can have status 01,02,05,05,03,05,05. My problem is how can I get a query which I run gives 03 as latest status not 05. Also when the status reaches 04 that record I don't need to get on retrieval. Any help will be great!!
You have to first remove all documents which have status 04. Then do a lookup on the same collection to get the remaining documents. Unwind it and filter out documents with status 05.
[
{
//Group records by DelNumber
$group: {
_id: '$DelNumber',
EventStatuses: {
$addToSet: '$Eventstatus'
}
}
}, {
// Remove any with status 04
$match: {
EventStatuses: {
$ne: '04'
}
}
}, {
// Looksup on same collection to get full document
$lookup: {
from: 'collection',
localField: '_id',
foreignField: 'DelNumber',
as: 'matched'
}
}, {
// Unwind the matched records
$unwind: {
path: '$matched'
}
}, {
$replaceRoot: {
newRoot: '$matched'
}
}, {
// Remove any record with status 05
$match: {
Eventstatus: {
$ne: '05'
}
}
}
]