Subtract time from array to an array MongoDB

Question

I have a data like this

"delivery": [{
        "status": "Not Started",
        "createdAt": {
            "$date": "2020-12-21T03:50:33.877Z"
        },
        "updatedAt": {
            "$date": "2020-12-21T03:50:33.877Z"
        }
    }, {
        "status": "Accepted",
        "updatedAt": {
            "$date": "2020-12-21T03:51:20.915Z"
        },
        "createdAt": {
            "$date": "2020-12-21T03:51:20.915Z"
        }
    }, {
        "status": "Ongoing",
        "updatedAt": {
            "$date": "2020-12-21T03:52:40.087Z"
        },
        "createdAt": {
            "$date": "2020-12-21T03:52:40.087Z"
        }
    }, {
        "status": "Ended",
        "updatedAt": {
            "$date": "2020-12-21T03:52:40.087Z"
        },
        "createdAt": {
            "$date": "2020-12-21T04:00:40.087Z"
        }

I want to get the time difference from Not Started to Accepted and from Ongoing to Ended. I'm having an error cant $subtract aarray from a array I am expecting to get the difference between two different from the arrays.

db.deliveries.aggregate([
    { 
        $project: 
        { 
            "delivery.createdAt": 1,
            "delivery.updatedAt": 1
        } 
    },
    { 
        $addFields: {
            difference: {
                $divide: [{$subtract: ["$delivery.updatedAt", "$delivery.createdAt"]}, 3600000]
            }
        }       
    },
])

Expected Output:

{
    "status": "Not Started",
    "createdAt": {
        "$date": "2020-12-21T03:50:33.877Z"
   },
   "updatedAt": {
        "$date": "2020-12-21T03:50:33.877Z"
    }
}, {
    "status": "Accepted",
    "updatedAt": {
        "$date": "2020-12-21T03:51:20.915Z"
    },
    "createdAt": {
        "$date": "2020-12-21T03:51:20.915Z"
    }
},
difference: 0.013066111111111112



{
    "status": "Ongoing",
    "updatedAt": {
        "$date": "2020-12-21T03:52:40.087Z"
    },
    "createdAt": {
        "$date": "2020-12-21T03:52:40.087Z"
    }
}, {
    "status": "Ended",
    "updatedAt": {
        "$date": "2020-12-21T03:52:40.087Z"
    },
    "createdAt": {
        "$date": "2020-12-21T04:00:40.087Z"
    },
 difference: 0.08 //sample computation

I want to calculate the difference from Not Started(createdAt) to Accepted(updatedAt). And get the difference from **Ongoing(createdAt) to Ended(updatedAt).

Answer 1

You could $addFields and $cond to find out the start and end time. Then compute the difference,

db.collection.aggregate([
  {
    "$addFields": {
      "start": {
        "$reduce": {
          "input": "$delivery",
          "initialValue": null,
          "in": {
            "$cond": {
              "if": {
                $eq: [
                  "$$this.status",
                  "Not Started"
                ]
              },
              "then": "$$this.createdAt",
              "else": "$$value"
            }
          }
        }
      },
      "end": {
        "$reduce": {
          "input": "$delivery",
          "initialValue": null,
          "in": {
            "$cond": {
              "if": {
                $eq: [
                  "$$this.status",
                  "Accepted"
                ]
              },
              "then": "$$this.updatedAt",
              "else": "$$value"
            }
          }
        }
      }
    }
  },
  {
    "$addFields": {
      "difference": {
        "$divide": [
          {
            "$subtract": [
              "$end",
              "$start"
            ]
          },
          3600000
        ]
      }
    }
  }
])

Here is the Mongo Playgroundfor your reference.