How to get the average of a value for each hour in a specific day in Mongo?

user_2661852 0
Viewed 455

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!

aggregation-framework mongodb
1 Answers

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 $avg
let result = db.collection.aggregate([
  {
    "$match": {
      "created_at": {
        "$gte": 1614211200,
        "$lt": 1614297599
      }
    }
  },
  {
    $group: {
      _id: { $hour: { $toDate: { $multiply: ["$created_at", 1000] } } },
      avg_value: { $avg: "$value" }
    }
  }
])

Playground

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);

user_89871280
Related