Mongodb aggregation "$project" 1 of 2 fields failing on 2nd field

Question

I have a data set that I am "mapping" into different names. Using Mongodb 7.0. I am using aggregate I am at the "$project stage

{ 
    _id: 0,
    "First Name": "Executive First Name",
    "Last Name": "Executive Last Name",
    "Title": { $switch: {
      branches: [
        {
          case: { $ne: [ "$Executive Title", null ] },
          then: "$Executive Title"
        },
      ],
      default: "$Professional Title"
    }}
}

So, my source data has long hairy label names, and my output (destined for a CSV import to another system) has different label names.

The $switch statement is killing me. When "$Executive Title" exists (and has a value) I get the "$Executive Title" in the "Title" field (label, whatever - I'm still trying to come up to speed on terminology for Mongodb).

When "$Executive Title" does not exist, It should populate the "Title" field with "$Professional Title". Instead, I get NO data and the field "Title" does not exist.

I have switched the 2 source fields ("$Executive Title" and "$Professional Title") and the behavior reverses.

I have tried $cond logic with the exact same results. the first mentioned field works, the second does not.

I have had the second 'case:' statement for "$Professional Title" but it doesn't change the results.

I don't know if $project as a stage doesn't allow a second reference to a different field in an assignment or what???? But the second reference always fails.

Example transformation:

{
    "_id": 1,
    "Executive First Name": "Bob",
    "Executive Last Name": "Crachet",
    "Executive Title": "Boss"
},
{
    "_id": 2,
    "Executive First Name": "Mary",
    "Executive Last Name": "Nolan",
    "Professional Title": "DDS"
}

transforms to:

{
    "First Name": "Bob",
    "Last Name": "Crachet",
    "Title": "Boss"
},
{
    "First Name": "Mary",
    "Last Name": "Nolan"
}

I would like the second record of the transformation to have the "Title": "DDS" entry....

I have been searching for hours but can't seem to come up the appropriate phrasing to get an answer.

I am not using db.collection.find. I am using db.collection.aggregation.

Answer 1

Starting from @ray solution, another solution is to use $ifNull directly without switch-case.

An example:

db.collection.aggregate([
  {
    "$project": {
      _id: 0,
      "First Name": "$Executive First Name",
      "Last Name": "$Executive Last Name",
      "Title": {
        $switch: {
          branches: [
            {
              case: {
                $ne: [
                  {
                    $ifNull: [
                      "$Executive Title",
                      null
                    ]
                  },
                  null
                ]
              },
              then: "$Executive Title"
            }
          ],
          default: "$Professional Title"
        }
      },
      "Title2": {
        $ifNull: [
          "$Executive Title",
          "$Professional Title"
        ]
      }
    }
  }
])

I added field Title2 that takes the first (ordered) not null value and return that on project.

MongoDB Playground