I have a collection in a MongoDB and I would like to get the average of the value in 24 hour time intervals for a given day.
Here is my collection:
[
{
"_id": 1,
"value": 50,
"created_at": 1614217700
},
{
"_id": 2,
"value": 60,
"created_at": 1614219300
},
{
"_id": 3,
"value": 100,
"created_at": 1614226200
},
{
"_id": 4,
"value": 80,
"created_at": 1614227400
}
]
The documents 3 & 4 are in the same hour slot, so the average of 80 and 100 is 90. Here is an example of what I expect as a result (or something similar).
[
{
"time": 0,
"avg_value": null
},
{
"time": 1,
"avg_value": 50
},
{
"time": 2,
"avg_value": 60
},
{
"time": 3,
"avg_value": null
},
{
"time": 4,
"avg_value": 90
},
,
,
,
{
"time": 23,
"avg_value": null
}
]
Here you can find my example: https://mongoplayground.net/p/YaAeebapbD-
Any ideas will be greatly appreciated. Thank you!
It is possible for available hours in database, for null values you need to prepare static array and merge with result,
$multiply created_at with 1000 to convert it to milliseconds$toDate to convert millisecnod to ISO date$hour to get hour form ISO date$group by hour that is above converted and average value using $avglet result = db.collection.aggregate([
{
"$match": {
"created_at": {
"$gte": 1614211200,
"$lt": 1614297599
}
}
},
{
$group: {
_id: { $hour: { $toDate: { $multiply: ["$created_at", 1000] } } },
avg_value: { $avg: "$value" }
}
}
])
Result will be,
[
{ "_id": 1, "avg_value": 50 },
{ "_id": 2, "avg_value": 60 },
{ "_id": 4, "avg_value": 90 }
]
Merge empty hours and result in client side, If you are using Javascript / NodeJs you can see example,
// RESULT FROM QUERY
let result = [
{ "_id": 1, "avg_value": 50 },
{ "_id": 2, "avg_value": 60 },
{ "_id": 4, "avg_value": 90 }
];
// GENERATE NULL HOURS FOR 23 HOURS IN ARRAY
let hours = [];
for (let i = 0; i<24; i++) hours.push({ _id: i, avg_value: null });
// MERGE WITH QUERY RESULT
let mergeArray = hours.map(h => {
let f = result.find(r => h._id === r._id);
return f ? f : h;
});
console.log(mergeArray);