Note

I am completely new to MongoDB so my terminology might not be fully correct.

Context

I have some data with various dates in MongoDB and I am trying to use a $switch function to provide column aliasing to form some new calculated columns however I am running into some issues (perhaps syntax or wrong implementation) and want to know why and how to resolve.

Question

Basically I want to create a new calculated date column which takes the value of the a date field if its not null, else it takes the value of another date field if its not null and so on, however it defaults to some message "blank" if there is no values in any of those fields.

Schema

A single document with the collection follows this rough structure

{
  _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"
}

Tried

As part of an aggregation pipeline I tried using the $project and then returning all the columns along with the new calculated columns however it comes up with an error at this stage saying stage must be a properly formatted document leading me to believe I either my syntax is wrong or perhaps using the wrong operators to do this.

If I remove the calculated column code then the preview seems to work (further convincing me its something to do with my calculated column implementation).

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

Update1: 2021-11-24 T12:15pm UTC

I have added in a missing curly bracket at the end of each of the case expressions. The error now is unknown operator: $date2

CodePudding user response：

change this case: {"$date4": {$ne: null}}, to case: {$ne:["$date4",null]}, in all of aggregate

CodePudding user response：

Description

It seems that the syntax of my statement is what was causing the issue (not sure why). If someone could elaborate that would be good.

Answer

Here is the code:

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"
        }
      }
    }
}
])

Notice that the {"$date_value": {$ne: null}} within each statement has been changed to {$ne:["$date_value",null]} possibly due to the $ne operator requiring 2 arguments?