CODEWITHSUNDEEP
mongodbmongooseaggregation-framework
jones
jones

Reputation: 1453

MongoDB date difference on each subdocument, and sum of them in final result

My user collection has an array field that contain sub documents like bellow:

"training" : [ 
  {
    "id" : ObjectId("5a01867959e2868ce09fbbc5"),
    "startDate" : ISODate("2012-12-18T00:00:00.000Z"),
    "endDate" : ISODate("2015-05-31T00:00:00.000Z"),
    "inProgress" : false
  }, 
  {
    "id" : ObjectId("5a01899959e2868ce09fbbc6"),
    "startDate" : ISODate("2017-11-02T00:00:00.000Z"),
    "endDate" : ISODate("2017-11-25T00:00:00.000Z"),
    "inProgress" : false
  },
  {
    "id" : ObjectId("5a01899959e2868ce09fbbc8"),
    "startDate" : ISODate("2018-01-02T00:00:00.000Z"),
    "endDate" : null,
    "inProgress" : true
  }
],

what i want is: 1- Get total training period of each user in year, month, and day (suppose git difference of first training, {year: 2, month: 6, day: 12}, second training {year: 0, month: 0, day: 23}, and last training that is an ongoing training then use from current date in subtract and should be {year: 0, month: 1, day: 15}) 2- Then I should calculate sum of each training period as a total training period.

What I have tried so far:

db.getCollection('user').aggregate([
   {$unwind: "$training"},
   {$project: {
      duration: {"$divide":[{$subtract: ['$training.to', '$education.from'] }, 1000 * 60 * 60 * 24 * 365]}
   }},
   {$group: {
     _id: '$_id',
     "duration": {$sum: '$duration'}  
   }}]
])

But this one has following problems: 1- can't calculate each training period in desired format separately, and can't calculate sum of those period as total training period. Because there may be a break between the training periods. 2- can't calculate in progress training duration.

Upvotes: 1

Views: 204

Answers (1)

Saravana
Saravana

Reputation: 12817

added multiple $addFields stage to reduce and compute difference in days, month, and years for each training and total training.

Here the assumption is 1 month = 30 days always

db.data.aggregate(
    [
        {
            $addFields : {
                trainingPeriod : {
                    $map : {
                        input : "$training",
                        as : "t",
                        in : {
                            year: {$subtract: [{$year : {$ifNull : ["$$t.endDate", new Date()]}}, {$year : "$$t.startDate"}]},
                            month: {$subtract: [{$month : {$ifNull : ["$$t.endDate", new Date()]}}, {$month : "$$t.startDate"}]},
                            dayOfMonth: {$subtract: [{$dayOfMonth : {$ifNull : ["$$t.endDate", new Date()]}}, {$dayOfMonth : "$$t.startDate"}]}
                        }
                    }
                }
            }
        },
        {
            $addFields : {
                trainingPeriod : {
                    $map : {
                        input : "$trainingPeriod",
                        as : "d",
                        in : {
                            year: "$$d.year",
                            month: {$cond : [{$lt : ["$$d.dayOfMonth", 0]}, {$subtract : ["$$d.month", 1]}, "$$d.month" ]},
                            day: {$cond : [{$lt : ["$$d.dayOfMonth", 0]}, {$add : [30, "$$d.dayOfMonth"]}, "$$d.dayOfMonth" ]}
                        }
                    }
                }
            }
        },
        {
            $addFields : {
                trainingPeriod : {
                    $map : {
                        input : "$trainingPeriod",
                        as : "d",
                        in : {
                            year: {$cond : [{$lt : ["$$d.month", 0]}, {$subtract : ["$$d.year", 1]}, "$$d.year" ]},
                            month: {$cond : [{$lt : ["$$d.month", 0]}, {$add : [12, "$$d.month"]}, "$$d.month" ]},
                            day: "$$d.day"
                        }
                    }
                }
            }
        },
        {
            $addFields : {
                total : {
                    $reduce : {
                        input : "$trainingPeriod",
                        initialValue : {year : 0, month : 0, day : 0},
                        in : {
                            year: {$add : ["$$this.year", "$$value.year"]},
                            month: {$add : ["$$this.month", "$$value.month"]},
                            day: {$add : ["$$this.day", "$$value.day"]}
                        }
                    }
                }
            }
        },
        {
            $addFields : {
                total : {
                    year : "$total.year",
                    month : {$add : ["$total.month", {$floor : {$divide : ["$total.day", 30]}}]},
                    day : {$mod : ["$total.day", 30]}
                }
            }
        },
        {
            $addFields : {
                total : {
                    year : {$add : ["$total.year", {$floor : {$divide : ["$total.month", 12]}}]},
                    month : {$mod : ["$total.month", 12]},
                    day : "$total.day"
                }
            }
        }
    ]
).pretty()

result

{
    "_id" : ObjectId("5a87fcf68a2c0b7c0666140f"),
    "training" : [
        {
            "id" : ObjectId("5a01867959e2868ce09fbbc5"),
            "startDate" : ISODate("2012-12-18T00:00:00Z"),
            "endDate" : ISODate("2015-05-31T00:00:00Z"),
            "inProgress" : false
        },
        {
            "id" : ObjectId("5a01899959e2868ce09fbbc6"),
            "startDate" : ISODate("2017-11-02T00:00:00Z"),
            "endDate" : ISODate("2017-11-25T00:00:00Z"),
            "inProgress" : false
        },
        {
            "id" : ObjectId("5a01899959e2868ce09fbbc8"),
            "startDate" : ISODate("2018-01-02T00:00:00Z"),
            "endDate" : null,
            "inProgress" : true
        }
    ],
    "trainingPeriod" : [
        {
            "year" : 2,
            "month" : 5,
            "day" : 13
        },
        {
            "year" : 0,
            "month" : 0,
            "day" : 23
        },
        {
            "year" : 0,
            "month" : 1,
            "day" : 16
        }
    ],
    "total" : {
        "year" : 2,
        "month" : 7,
        "day" : 22
    }
}
>

Upvotes: 1

Related Questions