mongoose group by every hour and fill empty hours with null

Viewed 36

I'm working on a project with mongoose and nodejs.

I want to get the data from one day split in every hour. And if there isn't any data I want the value to be null.

What I have so far:

const startOfDay = new Date(created_at);
startOfDay.setUTCHours(0, 0, 0, 0);

const endOfDay = new Date(created_at);
endOfDay.setUTCHours(23, 59, 59, 999);

      const x = await Collection.aggregate([
        {
          $match: {
            createdAt: { $gte: startOfDay, $lte: endOfDay },
          },
        },
        {
          $group: {
            _id: { $hour: "$createdAt" },
            count: { $sum: 1 },
            avg: { $avg: "$some_value" },
          },
        },

And I get following output:

[
    {
        "_id": 8,
        "count": 1,
        "avg": 10.2
    },
    {
        "_id": 15,
        "count": 2,
        "avg": 25
    },
    {
        "_id": 12,
        "count": 2,
        "avg": 30
    }
]

So the _id's are the hours and the other data is also correct. But what I want is:

{
    "count": 5,
    "avg_total": 90,
    "total": 2910,
    "data": [
        [
            {
                "_id": 0,
                "avg": 0,
                "count": 0
            },
            {
                "_id": 1,
                "avg": 0,
                "count": 0
            },
            ...
            {
                "_id": 7,
                "avg": 0,
                "count": 0
            },
            {
                "_id": 8,
                "count": 1,
                "avg": 10.2
            },
            ...
            {
                "_id": 23,
                "avg": 0,
                "count": 0
            }
        ]
    ]
}

Is there a way to achive this within the aggregation ?

0 Answers
Related