Mongodb Aggregation by 5 minutes

Question

I have IoT sensors which keep sending events every second, and storing the events in MongoDB below format:

{
  "_id" : "stp_23", 
    "HP" : [
      1261.0, 
      1357.0, 
      1337.0, 
      1250.0, 
      1269.0, 
      1134.0, 
      1219.0, 
      1269.0, 
      1166.0, 
      1361.0, 
      1246.0,
      ... ]
    "TS" : [
      "2019-12-20T00:00:04.6440124Z", 
      "2019-12-20T00:00:14.6440124Z", 
      "2019-12-20T00:00:24.6440124Z", 
      "2019-12-20T00:00:34.6450042Z", 
      "2019-12-20T00:00:44.6450042Z", 
      "2019-12-20T00:00:54.6450042Z", 
      "2019-12-20T00:01:55.6460113Z", 
      "2019-12-20T00:02:05.6460113Z", 
      "2019-12-20T00:02:15.6460113Z", 
      "2019-12-20T00:02:35.6460113Z"
      ... ]
    "_id" : "stp_24" : ....
  }

I need to create a aggregation every 5 minutes based on TS values. I have followed the below query to execute, since TS is array of timestamp getting Error

Unsupported conversion from array to date in $convert with no onError value

db.hp_daily.aggregate([
  { "$group": {
    "_id": {
      "$toDate": {
        "$subtract": [
          { "$toLong": { "$toDate": "$TS" }  },
          { "$mod": [ { "$toLong": { "$toDate": "$TS" } }, 1000 * 60 * 5 ] }
        ]
      }
    },
    "count": { "$sum": 1 }
  }}
])

I'm looking for a result like this:

[
  {
    "_id" : "stp_23",
    "ts" : ISODate("2019-12-20T00:05:00.000Z"),
    "avgHP" : 1200
  },
  {
    "_id" : "stp_24",
    "ts" : ISODate("2019-12-20T00:10:00.000Z"),
    "avgHP" : 1350
  }
]

The sample input document shows two `_id` fields. Also, the output you are expecting shows a `id` field. It is not clear what the relationship between the `_id`, `id` and the `TS` fields. Please explain clearly. — prasad_, Dec 25 '19 at 09:43

score 2 · Answer 1 · answered Dec 25 '19 at 12:44

ASSUMPTION

Your HP and TS has the same array size, each timestamp corresponds HP value
Modified last 3 timestamps (for current example) in order to get differente time range

SETUP

We need to change arrays into single object to apply $toDate
Also, we need to link each HP value for correspond TS value link
Then we apply your aggregation (changed a bit) and get desired result

db.hp_daily.aggregate([
  {
    $unwind: {
      path: "$HP",
      includeArrayIndex: "HP_index",

    }
  },
  {
    $unwind: {
      path: "$TS",
      includeArrayIndex: "TS_index",

    }
  },
  {
    $project: {
      HP: 1,
      TS: 1,
      compare: {
        $cmp: [
          "$HP_index",
          "$TS_index"
        ]
      }
    }
  },
  {
    $match: {
      compare: 0
    }
  },
  {
    $project: {
      _id: 1,
      HP: 1,
      TS: 1
    }
  },
  {
    "$group": {
      "_id": {
        _id: "$_id",
        "ts": {
          "$toDate": {
            "$subtract": [
              {
                "$toLong": {
                  "$toDate": "$TS"
                }
              },
              {
                "$mod": [
                  {
                    "$toLong": {
                      "$toDate": "$TS"
                    }
                  },
                  {
                    $multiply: [
                      1000,
                      60,
                      5
                    ]
                  }
                ]
              }
            ]
          }
        }
      },
      "avgHP": {
        "$avg": "$HP"
      }
    }
  },
  {
    $sort: {
      "_id._id": 1,
      "_id.ts": 1
    }
  },
  {
    $project: {
      _id: "$_id._id",
      ts: "$_id.ts",
      avgHP: "$avgHP"
    }
  }
])

[
  {
    "_id": "stp_23",
    "avgHP": 1261,
    "ts": ISODate("2019-12-20T00:00:00Z")
  },
  {
    "_id": "stp_23",
    "avgHP": 1265.3333333333333,
    "ts": ISODate("2019-12-20T00:05:00Z")
  }
]

MongoPlayground

Mongodb Aggregation by 5 minutes

1 Answers1

Linked