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 ?