I have 3 level nested array. I am trying an aggregation where I get only Extras that has any quotation (not empty).
The return must have only the last Quotation... If an Extra has 20 quotations, the return should be only the the array with one item (20th one).
I am trying the $slice function but is not working... what should I use?
In this example, should return only the item _id:1 with QuotationHistory with only one item inside (the last one)
I am using $slice or $lastN with no sucess. Should I unwind 3x to the level of the quotation and addFileds with another name using $slice?
Or should I user filter inside the array on the first stage and than do the other operations?
[
{
_id: 1,
Extras: [
{
Name: "NO",
QuotationHistory: []
},
{
Name: "NO",
QuotationHistory: null
},
{
Name: "NO",
NOQuotationHistory: true
},
{
Name: "OK",
QuotationHistory: [
{
Date: "2023-01-01",
Items: [
{
Name: "ERROR",
Price: 90
}
]
},
{
Date: "2023-01-02",
Items: [
{
Name: "LAST 200",
Price: 200
}
]
}
]
}
]
},
{
_id: 2,
Extras: [
{
Name: "OK",
QuotationHistory: [
{
Date: "2023-01-01",
Items: [
{
Name: "ERROR",
Price: 90
}
]
},
{
Date: "2023-01-02",
Items: [
{
Name: "LAST 100",
Price: 100
}
]
}
]
},
{
Name: "OK",
QuotationHistory: [
{
Date: "2023-01-01",
Items: [
{
Name: "ERROR",
Price: 100
}
]
},
{
Date: "2023-01-02",
Items: [
{
Name: "LAST 300",
Price: 300
}
]
}
]
}
]
},
{
_id: 4,
Extras: [
{
Name: "OK",
QuotationHistory: [
{
Date: "2023-01-01",
Items: [
{
Name: "ERROR",
Price: 90
}
]
},
{
Date: "2023-01-02",
Items: [
{
Name: "LAST 500",
Price: 500
}
]
}
]
}
]
},
{
_id: 5,
Extras: [
{
Name: "NO",
QuotationHistory: []
}
]
},
{
_id: 6,
Extras: []
},
{
_id: 7,
WithNoExtrasProp: true
}
]
CodePudding user response：
First $filter out Extras with no quotation history(i.e. $size is 0). Then, $match with non-empty Extras. Finally, use $mergeObjects with $last to update the Extras.QuotationHistory to its last entry.
db.collection.aggregate([
{
$match: {
$expr: {
$and: [
{
$ne: [
{
$ifNull: [
"$Extras",
[]
]
},
[]
]
},
{
$ne: [
{
$ifNull: [
"$Extras.QuotationHistory",
[
[]
]
]
},
[
[]
]
]
}
]
}
}
},
{
$set: {
Extras: {
QuotationHistory: {
$last: {
$last: "$Extras.QuotationHistory"
}
}
}
}
}
])