I am trying to apply a group by operation based on month from field From_Date and then calculate the sum of Gross_Amount, Net_Amount and Tax_Amount. Have a look at below mongoDB document sample:

{
    "Partner_ID" : "0682047456",
    "EarningsData" : [ 
        {
            "From_Date" : ISODate("2022-01-10T18:30:00.000Z"),
            "Gross_Amount" : 300,
            "Net_Amount" : 285,
            "Tax_Amount" : 15
        }, 
        {
            "From_Date" : ISODate("2022-10-01T18:30:00.000Z"),
            "Gross_Amount" : 1958,
            "Net_Amount" : 1860,
            "Quantity" : 979,
            "Tax_Amount" : 98
        }, 
    ],
    "createdAt" : ISODate("2023-01-23T16:23:02.430Z")
}

Below is the aggregation query which I have written :

var projectQry = [
        {
          $match: {
            "Partner_ID": userId
          }
        },
        {
          $unwind: "$EarningsData"
        },
        {
          $group: {
            _id: {
                $month: "$EarningsData.From_Date"
            },
            Gross: {
              $sum: "$EarningsData.Gross_Amount"
            },
            Tax: {
              $sum: "$EarningsData.Tax_Amount"
            },
            Net: {
              $sum: "$EarningsData.Net_Amount"
            },
          }
        },
        {
          $project: {
            _id: 0,
            Month: "$_id",
            Gross: 1,
            Tax: 1,
            Net: 1
          }
        }
      ];

Everything is working fine and I am getting the output also. But, I need to sort that output based on Month. I tried to apply sort pipeline at the end as follows

{
     $sort: {
        Month: 1
     }
},

But the problem happening here is previous year Dec month is coming after Jan month of current year.

NOTE: The From_Date field contains the date of either current year or last year only. It will never go beyond last year.

>Solution :

If I understand what you are trying to do, you should group by <year, month> and perform sorting on these fields.

Note:
Check the data you reported in the question as there are inconsistencies with your pipeline, however they are understandable.

The aggregation pipeline should look as follows:

db.getCollection("test01").aggregate([
    {
        $match: {
            "Partner_ID": "0682047456"
        }
    },
    {
        $unwind: "$EarningsData"
    },
    {
        $group: {
            _id: {
                year: { $year: "$EarningsData.From_Date", },
                month: { $month: "$EarningsData.From_Date" }
            },
            Gross: {
                $sum: "$EarningsData.Gross_Amount"
            },
            Tax: {
                $sum: "$EarningsData.Tax_Amount"
            },
            Net: {
                $sum: "$EarningsData.Net_Amount"
            },
        }
    },
    {
        $project: {
            _id: 0,
            Date: "$_id",
            Gross: 1,
            Tax: 1,
            Net: 1
        }
    },
    {
        $sort: {
            "Date.year": 1,
            "Date.month": 1,
        }
    }
]);