Mongodb Question posted in
The official documentation can be found here.

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

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.

Tags:

2

Answers


  1. 0

    From MongoDB official document, $ne is used for a field that exists. For your case, you can use $ifNull to fallback the non-existent field to a value of null.

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

    Mongo Playground

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search