mongoose - I want to return sum of values by month from JAN-DEC in a year in mongodb

Question

I want to write a Mongoose query to get the sum total for every month (Jan-Dec) in a year.

Currently the query I wrote only returns a value of the sum total for a month and skips all other months. I want a situation where all other months with no sum total should just return 0

Here is my query

db.getCollection('sales_records').aggregate([
{ $match: { createdDate: { $gt: ISODate('2020-01-01 00:00:00.000Z'), $lt: ISODate('2020-12-25 00:00:00.000Z')} } },
{ $match: { merchantId: 5547 } },
  {
    $group : {
      _id: {
          
        year: { $year : "$createdDate" },
        month: { $month : "$createdDate" },
        //week: { $week : "$createdDate" },
      // day: { $day : { $week : "$createdDate" } },
        //hour: { $hour : "$createdDate" },
      },
      merchantId: { $first:  "$merchantId"  },
      date: { $first:  "$createdDate"  },
      total: { $sum : "$totalAmount"}
    }
  },
  {
    $sort: { date: -1 }
  }
])



{
    "_id" : {
        "year" : 2020,
        "month" : 4
    },
    "merchantId" : NumberLong(5547),
    "date" : ISODate("2020-04-01T00:00:00.000Z"),
    "total" : 2100.0
}

/* 2 */
{
    "_id" : {
        "year" : 2020,
        "month" : 3
    },
    "merchantId" : NumberLong(5547),
    "date" : ISODate("2020-03-02T09:34:23.870Z"),
    "total" : 396511341.0
}

But I wanted to return something like this

{
    "_id" : {
        "year" : 2020,
        "month" : 6
    },
    "merchantId" : NumberLong(5547),
    "date" : ISODate("2020-04-01T00:00:00.000Z"),
    "total" : 0
}

{
    "_id" : {
        "year" : 2020,
        "month" : 5
    },
    "merchantId" : NumberLong(5547),
    "date" : ISODate("2020-04-01T00:00:00.000Z"),
    "total" : 0
}

{
    "_id" : {
        "year" : 2020,
        "month" : 4
    },
    "merchantId" : NumberLong(5547),
    "date" : ISODate("2020-04-01T00:00:00.000Z"),
    "total" : 2100.0
}

{
    "_id" : {
        "year" : 2020,
        "month" : 3
    },
    "merchantId" : NumberLong(5547),
    "date" : ISODate("2020-03-02T09:34:23.870Z"),
    "total" : 396511341.0
}
{
    "_id" : {
        "year" : 2020,
        "month" : 2
    },
    "merchantId" : NumberLong(5547),
    "date" : ISODate("2020-03-02T09:34:23.870Z"),
    "total" : 0
}
{
    "_id" : {
        "year" : 2020,
        "month" : 1
    },
    "merchantId" : NumberLong(5547),
    "date" : ISODate("2020-03-02T09:34:23.870Z"),
    "total" : 0
}

question from:https://stackoverflow.com/questions/65870238/i-want-to-return-sum-of-values-by-month-from-jan-dec-in-a-year-in-mongodb

Answer 1

Waitting for answers