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 sum of Net_Amount per Scheme_Name per month of From_Date for the specific Partner_ID.

For the above document, the output will look something like this:

[
  {
    "Month" : 1,
    "Scheme_Name" : 'CUSTOMERWINBACKJCA01'
    "Net_Amount": 256
  },  
  {
    "Month" : 2,
    "Scheme_Name" : 'CUSTOMERWINBACKJCA01'
    "Net_Amount": 285 
  } 
]

I have tried to implement the aggregation pipeline and was successfully able to get the sum of Net_Amount per Scheme_Name but I am not able to figure out how to integrate the per month of From_Date logic.

Below is the query sample:

var projectQry = [
        {
          "$unwind": {
            path : '$EarningsData',
            preserveNullAndEmptyArrays: true
          }
        },
        {
          $match: {
            "Partner_ID": userId
          }
        },
        {
          $group : {
            _id: "$EarningsData.Scheme_Name", 
            Net_Amount: { 
              $sum: "$EarningsData.Net_Amount" 
            }
          }
        },
        {
          $project: {
            _id: 0,
            Scheme_Name: "$_id",
            Net_Amount: 1
          }
        }
      ];

CodePudding user response：

You need to fix some issues,

1. $match move this stage to first for better performance, can use an index if you have created
2. $unwind doesn't need preserveNullAndEmptyArrays property, it preserves empty and null arrays
3. $group by Scheme_Name and From_Date as month, get sum of From_Date by $sum operator
4. $project to show required fields

db.collection.aggregate([
  { $match: { "Partner_ID": "662347848" } },
  { $unwind: "$EarningsData" },
  {
    $group: {
      _id: {
        Scheme_Name: "$EarningsData.Scheme_Name",
        Month: {
          $month: "$EarningsData.From_Date"
        }
      },
      Net_Amount: {
        $sum: "$EarningsData.Net_Amount"
      }
    }
  },
  {
    $project: {
      _id: 0,
      Net_Amount: 1,
      Scheme_Name: "$_id.Scheme_Name",
      Month: "$_id.Month"
    }
  }
])

Playground