Can you use $ifNull to conditionally pass a field through an aggregation pipeline?

Working with Data
#1

Would it be possible to use $ifNull to conditionally pass a field through an aggregation pipeline based on whether or not data is present in the field? I’m using an $project after an $sort, but I want to pass $trim:{input: $causeofdeath} through if data is in the field.

Can I do that, or do I have to pass the field through all the time?

/**
 * specifications: The fields to
 *   include or exclude.
 */
{
  cemetery: {
    $trim: {
      input: "$cemetery",
    },
  },
  lname: {
    $trim: {
      input: "$lname",
    },
  },
  fname: {
    $trim: {
      input: "$fname",
    },
  },
  causeofdeath: {
    $trim: {
      input: "$causeofdeath",
    },
  },
  ddate: 1,
  bdate: 1,
}
#2

Hello @Douglas_Carmichael, Welcome to the MongoDB community forum,

The $trim will return null if the field does not exist, or it is null, are you saying that you need to exclude the field in the result if it is null?

If yes then try this:

  {
    $project: {
      causeofdeath: {
        $ifNull: [
          { $trim: { input: "$causeofdeath" } },
          "$$REMOVE"
        ]
      }
    }
  }

The $$REMOVE will remove the property if the field’s value is null.

Out of the question, I would suggest you insert a trimmed value in the database, so you can avoid these extra operations when you retrieve the data from the database.

2 Likes
#3

Thanks! Unfortunately, I’m working with a “dirty” data set that has been mostly maintained by inexperienced people up to this point.

Would csvkit (csvkit 1.1.1 documentation) be able to trim the values on the CSV before I import them with mongoimport, or would I have to write my own script?

#4

I don’t know more about csvkit, but if you google it you will find plenty of resources/tools to trim the CSV values.

If you want to update your existing document’s value then you can use update with aggregation pipeline with updateMany() method and the solution that I have provided will work in update query.

1 Like
#5

How would I create the updateMany() query? Could I use Compass?

#6

Are you asking about how to create a query or where to execute a query?

You can execute it in the Compass shell and Mongo shell as well.

#7

How to create a query.

#8

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.