MongoDB aggregation problem in a span of time

Question

im trying to aggregate various sensors data but until now i've been unsuccessful.

The data schema is this:

{
   _id: "613b78bafd96452308232571",
   id_machine: "60ec5692a1796250f821222",
   time: "2021-09-10T15:24:42.365Z",
   value: [
      {cod: 'CO2', val: '29'}
      {cod: 'Temp', val: '29'}
      {cod: 'CO2', val: '29'}
      {cod: 'O3', val: '29'}
      {cod: 'PM2_5_In', val: '29'}
      {cod: 'PM2_5_Out', val: '29'}
      {cod: 'PM10_In', val: '29'}
      {cod: 'PM10_Out', val: '29'}
      {cod: 'Pa1', val: '29'}
      {cod: 'Pa2', val: '29'}
      {cod: 'UVC', val: '29'}
      {cod: 'Hum', val: '29'}
   ]
}

And what i would like is to have returned the average of each "cod" (avg of CO2, etc) in a given period of time.

Thank you in advanced

user_10770370 · Answer

What you want to do is first match the relevant documents in the timerange, then $group each measurement by itself. finally calculate the average score based on those calculations.

Like so:

db.collection.aggregate([
  {
    $match: {
      $expr: {
        $and: [
          {
            $gte: [
              {
                "$toDate": "$time"
              },
              startDate
            ]
          },
          {
            $lte: [
              {
                "$toDate": "$time"
              },
              endDate
            ]
          }
        ]
      }
    }
  },
  {
    $unwind: {
      path: "$value",
      
    }
  },
  {
    $group: {
      _id: "$value.cod",
      valueSum: {
        $sum: {
          "$toInt": "$value.val"
        }
      },
      docCount: {
        $sum: 1
      }
    }
  },
  {
    $group: {
      _id: null,
      value: {
        $push: {
          cod: "$_id",
          avgVal: {
            "$divide": [
              "$valueSum",
              "$docCount"
            ]
          }
        }
      }
    }
  }
])

Mongo Playground

Few things to note:

This assumes there are no "missing" measurement, meaning each document will have all of these fields. if the field is missing the $group will ignore it and it will not be calculated as 0 but rather than missing value. meaning the "avg" will not be reflected properly. (unless this is desired?)
I recommend you change the types in your DB to be ready for this query, you can see I used $toInt and $toDate to cast the types. this not only adds overhead but will make utilizing an index for efficient queries on the time field impossible.

user_6462538 · Answer

pls refer to https://mongoplayground.net/p/Htx5JqN06ob

Note: I assume value is array here.

    db.collection.aggregate([
  {
    $unwind: {
      path: "$value",
      
    }
  },
  {
    $group: {
      _id: {
        "$concat": [
          "$id_machine",
          "$time",
          "$value.cod"
        ]
      },
      Avg_val: {
        $avg: {
          $toInt: "$value.val"
        }
      },
      cod: {
        $first: "$value.cod"
      },
      id_machine: {
        $first: "$id_machine"
      },
      time: {
        "$first": "$time"
      }
    }
  }
])

Pipeline stages:

unwind: unwind values
group: group back based on the cod

MongoDB aggregation problem in a span of time

2 Answers