I have a series of documents in MongoDB that look like this:
{
"_id" : ObjectId("63ceb466db8c0f5500ea0aaa"),
"Partner_ID" : "662347848",
"EarningsData" : [
{
"From_Date" : ISODate("2022-01-10T18:30:00.000Z"),
"Scheme_Name" : "CUSTOMERWINBACKJCA01",
"Net_Amount" : 256,
},
{
"From_Date" : ISODate("2022-02-10T18:30:00.000Z"),
"Scheme_Name" : "CUSTOMERWINBACKJCA01",
"Net_Amount" : 285,
}
],
"createdAt" : ISODate("2023-01-23T16:23:02.440Z")
}
Now, what I need to do is to get the date of “Net_Amount” payment per month per “Scheme_Name”. This date and month will be from the field “From_Date”. Now, there could be multiple dates in a particular month on which the payment has been made for a scheme. We just need to get the latest date.
I have tried below query for the aggregation operation in mongo and I am getting the desired results but the problem is that I need to sort the Date array so that I can get the last date of the payment.
var projectQry = [
{
$match: {
"Partner_ID": userId
}
},
{
$unwind: "$EarningsData"
},
{
$group: {
_id: {
Scheme_Name: "$EarningsData.Scheme_Name",
Month: {
$month: "$EarningsData.From_Date"
}
},
Day: {
"$push": {
"$dayOfMonth": "$EarningsData.From_Date"
}
},
}
},
{
$project: {
_id: 0,
Scheme_Name: "$_id.Scheme_Name",
Month: "$_id.Month",
Day: 1
}
}
];