date calculations in $addFields

General Tech Bugs & Fixes 2 years ago

0 2 0 0 0 tuteeHUB earn credit +10 pts

5 Star Rating 1 Rating

Posted on 16 Aug 2022, this text provides information on Bugs & Fixes related to General Tech. Please note that while accuracy is prioritized, the data presented might not be entirely correct or up-to-date. This information is offered for general knowledge and informational purposes only, and should not be considered as a substitute for professional advice.

Take Quiz To Earn Credits!

Turn Your Knowledge into Earnings.

tuteehub_quiz

Answers (2)

Post Answer
profilepic.png
manpreet Tuteehub forum best answer Best Answer 2 years ago

I want to perform date calculations in the $addFields stage of the aggregation pipeline. This works with hard-coded multipliers but fails if I try to pass a value from a document.

The MongoDB version is Atlas 4.0.6.

Given this document structure:

{
    "_id" : ObjectId("5c9e78c61c9d440000a83cca"),
    "title" : "InterventionA",
    "thresholdCount" : 4,
    "thresholdUnit" : "weeks"
},
{
    "_id" : ObjectId("5c9e7d361c9d440000a83ccb"),
    "title" : "InterventionB",
    "thresholdCount" : 4,
    "thresholdUnit" : "days"
}

..this query works. Notice the (*4) multiplier in the $cond is hard coded.

const endDate = new Date();
endDate.setHours(0, 0, 0, 0);

const ms1d = 24 * 60 * 60 * 1000;   /* milliseconds per day */
const ms1w = 7 * ms1d;              /* milliseconds per week */

db.stackex.aggregate([
  {
    $addFields: {
      dateRange: {
        $cond: {
          if: { $eq: ["$thresholdUnit", "weeks"] },
          then: { "start": { $subtract: [endDate, ms1w * 4] }, "end": endDate},
          else: { "start": { $subtract: [endDate, ms1d * 4] }, "end": endDate}
        }
      }
    }
  }
]);

The desired results are:

{
    "_id" : ObjectId("5c9e78c61c9d440000a83cca"),
    "title" : "InterventionA",
    "thresholdCount" : 4,
    "thresholdUnit" : "weeks",
    "dateRange" : {
        "start" : ISODate("2019-02-28T23:00:00.000-08:00"),
        "end" : ISODate("2019-03-29T00:00:00.000-07:00")
    }
},
{
    "_id" : ObjectId("5c9e7d361c9d440000a83ccb"),
    "title" : "InterventionB",
    "thresholdCount" : 4,
    "thresholdUnit" : "days",
    "dateRange" : {
        "start" : ISODate("2019-03-25T00:00:00.000-07:00"),
        "end" : ISODate(
                                                
                                                
0 views
0 shares
profilepic.png
manpreet 2 years ago

You need to use the $multiply operator for the multiplication calculation.

So replace ms1w * "$thresholdCount" with { $multiply: [ms1w, "$thresholdCount"] }

Full $cond expression here:

$cond: {
    if: { $eq: ["$thresholdUnit", "weeks"] },
    then: { "start": { $subtract: [endDate, { $multiply: [ms1w, "$thresholdCount"] } ] }, "end": endDate},
    else: { "start": { $subtract: [endDate, { $multiply: [ms1d, "$thresholdCount"] } ] }, "end": endDate}
}

0 views   0 shares

No matter what stage you're at in your education or career, TuteeHub will help you reach the next level that you're aiming for. Simply,Choose a subject/topic and get started in self-paced practice sessions to improve your knowledge and scores.