Fetching data from the timeseries

Working with Data
,
1

Here is my timeseries schema:
{
“datetime” : ISODate(“2024-03-18T14:34:00.748+0000”),
“macid” : “65d43e4c0501fa25f070bc35”,
“params” : [
{
“name” : “cpu”,
“value” : NumberInt(50)
},
{
“name” : “memory”,
“value” : NumberInt(32)
},
{
“name” : “storage”,
“value” : NumberInt(60)
}
]
}
I would like to apply aggregation on the params(i.e array of param)

2

Hello @Sureshgoud_Bhimagonolla :wave:
Could you please elaborate more, on what you want, and which type of data you want to filter out?

3

Hi,
I would like to aggregate (either avg ) the params by every 15 mins. So that, for 15 mins will get to know the usage of the system.

Group by all the params in the params array. Its timeseries collection

4

hey @Sureshgoud_Bhimagonolla ,
Does the below aggregation help what you are expecting?

db.timeseries.aggregate([
  {
    $match: {
      datetime: {
        $gte: ISODate("2024-03-18T00:00:00Z"), // Start of the day
        $lt: ISODate("2024-03-19T00:00:00Z") // End of the day
      }
    }
  },
  {
    $addFields: {
      // Extract the minute component from the datetime field
      minute: { $minute: "$datetime" }
    }
  },
  {
    $addFields: {
      // Calculate the interval by rounding down the minute to the nearest 15-minute mark
      interval: {
        $subtract: [
          "$minute",
          { $mod: ["$minute", 15] } // Round down to the nearest 15
        ]
      }
    }
  },
  {
    $group: {
      _id: {
        interval: {
          $dateFromParts: {
            // Create a new Date object with the year, month, day, hour, and calculated minute
            year: { $year: "$datetime" },
            month: { $month: "$datetime" },
            day: { $dayOfMonth: "$datetime" },
            hour: { $hour: "$datetime" },
            minute: "$interval"
          }
        }
      },
      // Calculate the average value for each parameter within the 15-minute interval
      cpu_avg: { $avg: { $arrayElemAt: ["$params.value", { $indexOfArray: ["$params.name", "cpu"] }] } },
      memory_avg: { $avg: { $arrayElemAt: ["$params.value", { $indexOfArray: ["$params.name", "memory"] }] } },
      storage_avg: { $avg: { $arrayElemAt: ["$params.value", { $indexOfArray: ["$params.name", "storage"] }] } }
    }
  },
  {
    $project: {
      _id: 0, // Exclude _id field from output
      datetime: "$_id.interval",
      cpu_avg: 1,
      memory_avg: 1,
      storage_avg: 1
    }
  },
  {
    $sort: { datetime: 1 } // Sort by datetime in ascending order
  }
]);