MongoDB: Creating a calculated field measuring the difference between date fields

Question

Context

This question uses the same collection/ document schema construct from my other question: MongoDB: Creating calculated fields using the switch function (column aliasing)

Data Schema

Note: cal_date1 is the calculated value shown in my other post linked above. When performing these steps they are done in a pipeline so that (I hope) the calculated columns can be thought of as part of the general document schema as shown below.

{
  _id:ObjectId("619756f12c115f24df503c26"),
  uniqueid:"12345678",
  date1:"2021-11-02 20:04:50.253",
  date2:"2021-11-03 18:10:57.520",
  date3:"2021-11-08 07:08:00.000",
  date4:"2021-11-08 14:40:00.000",
  date5:"2021-11-08 08:34:00.000",
  cal_date1: "2021-11-03 18:10:57.520"
}

Questions

How can I calculate the difference in date values between two particular dates (with some conditional logic behind it).

Extension

Is there a way of calculate the working days between two dates where "working" is defined as dates that are inclusive of Mon-Fri (no Sat, Sun)?

Tried

I have been playing with the $dateDiff operator inside a switch function as shown below however come across the error unknown operator: $cal_date1

db.collection.aggregate([
{
$project:
      {
      "uniqueid": 1,
      "date1": 1,
      "date2": 1,
      "date3": 1,
      "date4":1,
      "date5": 1,
      "cal_date1": {
          $switch: {
            branches: [
                { case: {$ne:["$date2",null]}, then: "$date2"},
                { case: {$ne:["$date3",null]}, then: "$date3"},
                { case: {$ne:["$date4",null]}, then: "$date4"},
                { case: {$ne:["$date5",null]}, then: "$date5"}
            ],
            default: "blank"
          }
        },
      "cal_date2": {
          $switch: {
            branches: [
                { case: {$ne:["$date4",null]}, then: "$date4"},
                { case: {$ne:["$date4",null]}, then: "$date5"}
            ],
            default: "blank"
          }
        },
       "cal_date3": {
          $switch: {
            branches: [
                { case: {$ne:["$date5",null]}, then: "$date5"}
            ],
            default: "blank"
        }
      }
    }
}
])

--updated code
"cal_days_between_date1_caldate1": {
    $switch:
    {
     branches: [
     {case: { $eq: ["$date1", null]}, then: "blank"},
     {case: { $eq: ["$cal_date1", "blank"]}, then: "blank"} 
     ],
default: {
          $dateDiff: {
            startDate: {
              $dateFromString: {
                dateString: "$date1"
              }
            },
            endDate: {
              $dateFromString: {
                dateString: "$cal_date1"
              }
            },
            unit: "day"
          }
        }
    }
}

Update: 2021-11-24: T3:08pm UTC

Have altered the case expression slightly to try and get a boolean output and now have a new error of unknown operator: $dateDiff

Update: 2021-11-30: T8:36pm UTC

Have added in the code which contains the referenced fields (top of code chunk) along with the additions of the $dateFromString operator. The calculated date code is meant to act as the next step in the pipeline which I output along with the other fields.

Answer 1

The problem is that you are trying to use Date operators on fields that are strings.

You should transforms your strings to dates before with $dateFromString. Check this playground and docs. Also there was some mistakes in the $eq arrays

db.collection.aggregate({
  "$project": {
    "cal_days_between_date1_caldate1": {
      $switch: {
        branches: [
          {
            case: {
              $eq: [
                "$date1",
                null
              ]
            },
            then: "blank"
          },
          {
            case: {
              $eq: [
                "$cal_date1",
                "blank"
              ]
            },
            then: "blank"
          }
        ],
        default: {
          $dateDiff: {
            startDate: {
              $dateFromString: {
                dateString: "$date1"
              }
            },
            endDate: {
              $dateFromString: {
                dateString: "$cal_date1"
              }
            },
            unit: "day"
          }
        },
        
      }
    }
  }
})