mongodb

mongodb-query

aggregation-framework

mongoose

I want to find out the dwell time between every presenceStatus change.

Example collection -

   /* 1 */

{
    "_id" : ObjectId("5e4889a7c7959f6a13039902"),
    "presenceStatus" : 0,
    "createdAt" : ISODate("2020-02-16T00:14:35.121Z"),
    "updatedAt" : ISODate("2020-02-16T00:14:35.121Z"),
    "__v" : 0
}
/* 2 */
{
    "_id" : ObjectId("5e4889a7c7959f6a1303990c"),
    "presenceStatus" : 1,
    "createdAt" : ISODate("2020-02-16T00:15:35.121Z"),
    "updatedAt" : ISODate("2020-02-16T00:15:35.121Z"),
    "__v" : 0
}
/* 3 */

{
    "_id" : ObjectId("5e4889a9c7959f6a1303995c"),
    "presenceStatus" : 1,
    "sensingTime" : ISODate("2020-02-16T00:15:37.000Z"),
    "createdAt" : ISODate("2020-02-16T00:15:37.420Z"),
    "updatedAt" : ISODate("2020-02-16T00:15:37.420Z"),
    "__v" : 0
}

/* 4 */
{
    "_id" : ObjectId("5e4889b0c7959f6a130399ff"),
    "presenceStatus" : 1,
    "createdAt" : ISODate("2020-02-16T00:15:44.316Z"),
    "updatedAt" : ISODate("2020-02-16T00:15:44.316Z"),
    "__v" : 0
}

/* 5 */
{
    "_id" : ObjectId("5e4889b3c7959f6a13039a58"),
    "presenceStatus" : 1,
    "sensingTime" : ISODate("2020-02-16T00:15:47.000Z"),
    "createdAt" : ISODate("2020-02-16T00:15:47.181Z"),
    "updatedAt" : ISODate("2020-02-16T00:15:47.181Z"),
    "__v" : 0
}

/* 6 */
{
    "_id" : ObjectId("5e4889b5c7959f6a13039aad"),
    "presenceStatus" : 1,
    "sensingTime" : ISODate("2020-02-16T00:15:49.000Z"),
    "createdAt" : ISODate("2020-02-16T00:15:49.545Z"),
    "updatedAt" : ISODate("2020-02-16T00:15:49.545Z"),
    "__v" : 0
}

/* 7 */
{
    "_id" : ObjectId("5e4889b9c7959f6a13039b28"),
    "presenceStatus" : 1,
    "sensingTime" : ISODate("2020-02-16T00:15:53.000Z"),
    "createdAt" : ISODate("2020-02-16T00:15:53.389Z"),
    "updatedAt" : ISODate("2020-02-16T00:15:53.389Z"),
    "__v" : 0
}

/* 8 */
{
    "_id" : ObjectId("5e4889bcc7959f6a13039b78"),
    "presenceStatus" : 1,
    "sensingTime" : ISODate("2020-02-16T00:15:56.000Z"),
    "createdAt" : ISODate("2020-02-16T00:15:56.007Z"),
    "updatedAt" : ISODate("2020-02-16T00:15:56.007Z"),
    "__v" : 0
}

/* 9 */
{
    "_id" : ObjectId("5e4889bfc7959f6a13039c00"),
    "presenceStatus" : 1,
    "sensingTime" : ISODate("2020-02-16T00:15:59.000Z"),
    "createdAt" : ISODate("2020-02-16T00:15:59.619Z"),
    "updatedAt" : ISODate("2020-02-16T00:15:59.619Z"),
    "__v" : 0
}
/* 10 */
{
    "_id" : ObjectId("5e4889c2c7959f6a13039c4a"),
    "presenceStatus" : 0,
    "sensingTime" : ISODate("2020-02-16T00:16:02.000Z"),
    "createdAt" : ISODate("2020-02-16T00:16:02.100Z"),
    "updatedAt" : ISODate("2020-02-16T00:16:02.100Z"),
    "__v" : 0
}

As you can see presenceStatus changed in 2nd document from value 0 to 1.

I want to record this dwell Time period when the status remained 1. (basically time difference between status = 1 to status = 0 )

The same process starts again when it finds presenceStatus 1 again and records the time-period when presenceStatus remained 1 for that chunk of data.

Result collection would look like -

{
    "_id" : xxx
    "occupiedTime" : ISODate("2020-02-16T00:15:35.121Z"), //  *updatedAt* value of document 2, cause that's when it changed to 1. 

    "vacantTime" : ISODate("2020-02-16T00:16:02.100Z"), // *updatedAt* of document 10, because that's when status changed from 0 to 1. 
    "dwellTime" : (vacant time - occupied time in HH:MM:SS)
    "created" : "2019-05-29 07:08:13",
    "__v" : 0
}

Your help is appreciated.

Solution 1

Check if this solution meets your requirements.

Explanation

  1. We join over the same collection. So for each item i we take item i+1. This method gives us where presenceStatus has been changed.
  2. We filter documenti i+1 pairs where presenceStatus is 0 - 1 or 1 - 0.
  3. We group them into single data array.
  4. Now we iterate over data by 2 steps (i=0;i<data.length;i+=2) and take updatedAt value.
    var occupiedTime = data[i].tmp.updatedAt
    var vacantTime   = data[i+1].tmp.updatedAt
  1. We flatten calculated values and restore original document structure.

db.collection.aggregate([
  {
    $lookup: {
      from: "collection",
      let: {
        root_id: "$_id"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $gt: [
                "$_id",
                "$$root_id"
              ]
            }
          }
        },
        {
          $limit: 1
        }
      ],
      as: "tmp"
    }
  },
  {
    $match: {
      $or: [
        {
          "presenceStatus": 1,
          "tmp.presenceStatus": 0
        },
        {
          "presenceStatus": 0,
          "tmp.presenceStatus": 1
        }
      ]
    }
  },
  {
    $group: {
      _id: null,
      data: {
        $push: {
          $mergeObjects: [
            "$$ROOT",
            {
              tmp: {
                $arrayElemAt: [
                  "$tmp",
                  0
                ]
              }
            }
          ]
        }
      }
    }
  },
  {
    $addFields: {
      data: {
        $map: {
          input: {
            $range: [
              0,
              {
                $size: "$data"
              },
              2
            ]
          },
          as: "idx",
          in: {
            "occupiedTime": {
              $arrayElemAt: [
                "$data.tmp.updatedAt",
                {
                  $cond: [
                    {
                      $eq: [
                        {
                          $arrayElemAt: [
                            "$data.tmp.presenceStatus",
                            "$$idx"
                          ]
                        },
                        1
                      ]
                    },
                    "$$idx",
                    {
                      $add: [
                        "$$idx",
                        1
                      ]
                    }
                  ]
                }
              ]
            },
            "vacantTime": {
              $arrayElemAt: [
                "$data.tmp.updatedAt",
                {
                  $cond: [
                    {
                      $eq: [
                        {
                          $arrayElemAt: [
                            "$data.tmp.presenceStatus",
                            "$$idx"
                          ]
                        },
                        0
                      ]
                    },
                    "$$idx",
                    {
                      $add: [
                        "$$idx",
                        1
                      ]
                    }
                  ]
                }
              ]
            },
            "created": {
              $arrayElemAt: [
                "$data.tmp.createdAt",
                "$$idx"
              ]
            },
            "_id": {
              $arrayElemAt: [
                "$data.tmp._id",
                "$$idx"
              ]
            },
            "__v": 0
          }
        }
      }
    }
  },
  {
    $unwind: "$data"
  },
  {
    $replaceRoot: {
      newRoot: "$data"
    }
  },
  {
    $addFields: {
      "dwellTime": {
        $dateToString: {
          date: {
            $toDate: {
              $subtract: [
                "$vacantTime",
                "$occupiedTime"
              ]
            }
          },
          format: "%H-%M-%S"
        }
      }
    }
  }
])

MongoPlayground

Solution 2

@Valijon, @Plancke Thanks all who helped. we chose to take a different approach and turned our a simple for loop was able to do the job for now. Thanks again. Here is final solution if any one was interested:

let prevSensingResults = {}; 

db.sensingresults.find({updatedAt : {$gt :"",$lte : "")}, presenceStatus : {$exists: 1}}).sort({updatedAt:1})
.forEach(function(doc) { 
    if (typeof prevSensingResults[doc.deviceId.toString()] !== undefined) {
         if (prevSensingResults[doc.deviceId.toString()].presenceStatus !== doc.presenceStatus && doc.presenceStatus === 0) {
             db.presenceagg.update({accountId: doc.accountId, buildingId: doc.buildingId, gatewayId: doc.gatewayId, deviceId: doc.deviceId, occupiedTime: prevSensingResults[doc.deviceId.toString()].updatedAt, vacantTime: doc.updatedAt}
         , {accountId: doc.accountId, buildingId: doc.buildingId, gatewayId: doc.gatewayId, deviceId: doc.deviceId, occupiedTime: prevSensingResults[doc.deviceId.toString()].updatedAt, vacantTime: doc.updatedAt, dwellPeriodInSeconds: (doc.updatedAt.getTime() - prevSensingResults[doc.deviceId.toString()].updatedAt.getTime()) / 1000}
                 , {upsert:true});
            prevSensingResults[doc.deviceId.toString()] = doc;
         } else if (prevSensingResults[doc.deviceId.toString()].presenceStatus !== doc.presenceStatus && doc.presenceStatus === 1) 
            prevSensingResults[doc.deviceId.toString()] = doc;
      }
    } else {
        prevSensingResults[doc.deviceId.toString()] = doc;
   }
})

Solution 3

You could use a Map-Reduce on the collection, this will output the difference in seconds, but that should be relatively easy to change on your end.

I haven't used this method on any sizeable collections, nor at all recently, so I'm not aware of performance implications by doing it in this fashion, please refer to the docs for more information.

db.collection.mapReduce(
  function() {
    emit(0, this);
  },
  function(key,values){
    var state = values[0].presenceStatus;
    var stateTimestamp = values[0].updatedAt;
    var result = {
      changes: []
    };

    for (var i = 1; i < values.length; i++){
      var value = values[i];
      if (value.presenceStatus !== state) {
        if (state === 1) {
          result.changes.push({
            "dwellTime": value.updatedAt - stateTimestamp,
            "occupiedTime": value.updatedAt,
          });
        }

        state = value.presenceStatus;
        stateTimestamp = value.updatedAt;
      }
    }

    return result;
  },
  {
    out: { inline: 1 }
  }
).results[0].value.changes;
[
        {
                "dwellTime" : 26979,
                "occupiedTime" : ISODate("2020-02-16T00:16:02.100Z")
        }
]