Mongodb aggregation (months) query set "missing fields" to zero

user_14389341 0
Viewed 426

I have an aggregation query that groups all documents by month for a year.

db.collection.aggregate([
  {
    $match: {
      dos: { $gte: startOfYear(new Date()), $lte: endOfYear(new Date()) },
     },
  },
  {
    $group: {
      _id: {
        $month: "$dos"
      },
      count: {
        $sum: 1
      }, 
    },
  },
  {
    $project: {
      month: "$_id",
      count: true,
      _id: false,
      
    },
  },
  {
    $sort: {
      month: 1,
      
    }, 
  },
])

This results to 3 items (January, February, March). Each of the have a single document which was done during that month.

[
  {
    "count": 1,
    "month": 1
  },
  {
    "count": 1,
    "month": 2
  },
  {
    "count": 1,
    "month": 3
  }
]

What I want is to add the missing fields which are April to December with a { count: 0 month: 4 }, { count: 0, month: 5 } and etc etc...

[
  {
    "count": 1,
    "month": 1
  },
  {
    "count": 1,
    "month": 2
  },
  {
    "count": 1,
    "month": 3
  },
  {
    "count": 0,
    "month": 4
  },
  {
    "count": 0,
    "month": 5
  },
  ...,
  { 
    "count": 0,
    "month": 12,
  }
]

Is this possible?

You can play with the code right here. https://mongoplayground.net/p/ROjMQglxTjy

mongodb-query mongodb
1 Answers

It is possible because the length of the output is always fixed. So the idea is to generate an array of length 12 and then fill those months which has data otherwise empty count. So try this:

SOLUTION #1

db.calendar.aggregate([
    // Put your match stage here.
    {
        $group: {
            _id: { $month: "$dos" },
            count: { $sum: 1 }
        },
    },
    {
        $group: {
            _id: null,
            array: { $push: "$$ROOT" }
        }
    },
    {
        $addFields: {
            array: {
                $map: {
                    input: [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12],
                    as: "month",
                    in: {
                        $cond: {
                            if: { $in: ["$$month", "$array._id"] },
                            then: {
                                $arrayElemAt: [
                                    "$array",
                                    { $indexOfArray: ["$array._id", "$$month"] }
                                ]
                            },
                            else: {
                                _id: "$$month",
                                count: 0
                            }
                        }
                    }
                }
            }
        }
    },
    { $unwind: "$array" },
    {
        $sort: { "array._id": 1 }
    },
    {
        $project: {
            _id: 0,
            count: "$array.count",
            month: "$array._id"
        }
    }
]);

SOLUTION #2: Much efficient compared to first one.

db.calendar.aggregate([
    // Put your match stage here.
    {
        $group: {
            _id: { $month: "$dos" },
            count: { $sum: 1 }
        },
    },
    {
        $group: {
            _id: null,
            array: { $push: "$$ROOT" }
        }
    },
    {
        $addFields: {
            array: {
                $map: {
                    input: [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12],
                    as: "month",
                    in: {
                        $let: {
                            vars: {
                                index: { $indexOfArray: ["$array._id", "$$month"] }
                            },
                            in: {
                                $cond: [
                                    { $gt: ["$$index", -1] },
                                    { $arrayElemAt: ["$array", "$$index"] },
                                    { _id: "$$month", count: 0 }
                                ]
                            }
                        }
                    }
                }
            }
        }
    },
    { $unwind: "$array" },
    {
        $sort: { "array._id": 1 }
    },
    {
        $project: {
            _id: 0,
            count: "$array.count",
            month: "$array._id"
        }
    }
]);

Output:

/* 1 */
{
    "count" : 2,
    "month" : 1
},

/* 2 */
{
    "count" : 2,
    "month" : 2
},

/* 3 */
{
    "count" : 1,
    "month" : 3
},

/* 4 */
{
    "count" : 0,
    "month" : 4
},

/* 5 */
{
    "count" : 0,
    "month" : 5
},

/* 6 */
{
    "count" : 0,
    "month" : 6
},

/* 7 */
{
    "count" : 0,
    "month" : 7
},

/* 8 */
{
    "count" : 1,
    "month" : 8
},

/* 9 */
{
    "count" : 0,
    "month" : 9
},

/* 10 */
{
    "count" : 1,
    "month" : 10
},

/* 11 */
{
    "count" : 0,
    "month" : 11
},

/* 12 */
{
    "count" : 0,
    "month" : 12
}

Test data:

/* 1 createdAt:3/17/2021, 7:27:28 PM*/
{
    "_id" : ObjectId("60520ac8479cd440a079c271"),
    "dos" : ISODate("2021-01-15T00:00:00.000+05:30")
},

/* 2 createdAt:3/17/2021, 7:27:28 PM*/
{
    "_id" : ObjectId("60520ac8479cd440a079c272"),
    "dos" : ISODate("2021-03-03T00:00:00.000+05:30")
},

/* 3 createdAt:3/17/2021, 7:27:28 PM*/
{
    "_id" : ObjectId("60520ac8479cd440a079c273"),
    "dos" : ISODate("2021-02-14T00:00:00.000+05:30")
},

/* 4 createdAt:3/17/2021, 7:27:28 PM*/
{
    "_id" : ObjectId("60520ac8479cd440a079c274"),
    "dos" : ISODate("2021-01-26T00:00:00.000+05:30")
},

/* 5 createdAt:3/17/2021, 7:27:28 PM*/
{
    "_id" : ObjectId("60520ac8479cd440a079c275"),
    "dos" : ISODate("2021-02-28T00:00:00.000+05:30")
},

/* 6 createdAt:3/17/2021, 7:27:28 PM*/
{
    "_id" : ObjectId("60520ac8479cd440a079c276"),
    "dos" : ISODate("2021-08-15T00:00:00.000+05:30")
},

/* 7 createdAt:3/17/2021, 7:27:28 PM*/
{
    "_id" : ObjectId("60520ac8479cd440a079c277"),
    "dos" : ISODate("2021-10-02T00:00:00.000+05:30")
}
user_50704600
Related