I'm doing a small IoT project, and I want to show some useful informations that I get from the device.

The device is a switch button that controls one light bulb, each time the switch is activated, an event is sent to the database, here is two examples of the documents received:

{
    "_id" : ObjectId("62baf5b98218da3bc80e9d61"),
    "light_on" : false,
    "device_id" : "ab082e4d-e617-4c74-8d41-fa316246afac",
    "timestamp" : ISODate("2022-07-01T10:00:24.000 0000")
}

{
    "_id" : ObjectId("62baf5b98218da3bc90e9d6a"),
    "light_on" : true,
    "device_id" : "ab082e4d-e617-4c74-8d41-fa316246afac",
    "timestamp" : ISODate("2022-07-01T10:32:00.000 0000")
}

From this data, I want to extract:

1. The mean duration of when the light was on
2. The longest duration of the lights on
3. Longest duration of lights off
4. All duration that exceeded 4 hours of lightning on

This kind of request needs me to compare the documents two by two, which I never did in Mongo, and I'm not even sure it's doable, so any help is welcome.

CodePudding user response：

Try this one:

db.collection.aggregate([
  {
    $setWindowFields: {
      partitionBy: "$device_id",
      sortBy: { timestamp: 1 },
      output: {
        following: {
          $shift: {
            output: "$timestamp",
            by: 1
          }
        }
      }
    }
  },
  {
    $set: {
      duration: {
        $dateDiff: {
          startDate: "$timestamp",
          endDate: "$following",
          unit: "second"
        }
      }
    }
  },
  {
    $facet: {
      mean: [
        {
          $group: {
            _id: {
              device_id: "$device_id",
              light_on: "$light_on"
            },
            mean: { $avg: "$duration" },
            longest: { $max: "$duration" }
          }
        }
      ],
      exceeded: [
        {
          $match: {
            light_on: true,
            duration: { $gt: 14400 } // 4 hours = 14400 seconds
          }
        }
      ]
    }
  }
])

Mongo Playground

For MongoDB version < 5.0 you need a different solution. As starting point is this one:

db.collection.aggregate([
  { $sort: { timestamp: -1 } },
  {
    $group: {
      _id: "$device_id",
      timestamp: { $push: "$$ROOT" }
    }
  },
  {
    $set: {
      timestamp: {
        $reduce: {
          input: "$timestamp",
          initialValue: [],
          in: {
            $concatArrays: [
              "$$value",
              [
                {
                  $mergeObjects: [
                    "$$this",
                    { following: { $last: "$$value.timestamp" },
                      duration: {
                        $dateDiff: {
                          endDate: { $last: "$$value.timestamp" },
                          startDate: "$$this.timestamp",
                          unit: "second"
                        }
                      }
                    }
                  ]
                }
              ]
            ]
          }
        }
      }
    }
  },
  ... some cosmetic, see above
])