Remove all fields that are null

Question

How can I remove all fields that are null from all documents of a given collection?

---

I have a collection of documents such as:

{
    'property1': 'value1',
    'property2': 'value2',
    ...
}

but each document may have a null entry instead of a value entry.

I would like to save disk space by removing all null entries. The existence of the null entries does not contain any information in my case because I know the format of the JSON document a priori.

Answer 1

// run in mongo shell  

var coll = db.getCollection("collectionName");
var cursor = coll.find();
while (cursor.hasNext()) {
    var doc = cursor.next();
    var keys = {};
    var hasNull = false;
    for ( var x in doc) {
        if (x != "_id" && doc[x] == null) {
            keys[x] = 1;
            hasNull = true;
        }
    }
    if (hasNull) {
        coll.update({_id: doc._id}, {$unset:keys});
    }
}

Answer 2

Starting Mongo 4.2, db.collection.update() can accept an aggregation pipeline, finally allowing the removal of a field based on its value:

// { _id: ObjectId("5d0e8...d2"), property1: "value1", property2: "value2" }
// { _id: ObjectId("5d0e8...d3"), property1: "value1", property2: null, property3: "value3" }
db.collection.update(
  {},
  [{ $replaceWith: {
    $arrayToObject: {
      $filter: {
        input: { $objectToArray: "$$ROOT" },
        as: "item",
        cond: { $ne: ["$$item.v", null] }
      }
    }
  }}],
  { multi: true }
)
// { _id: ObjectId("5d0e8...d2"), property1: "value1", property2: "value2" }
// { _id: ObjectId("5d0e8...d3"), property1: "value1", property3: "value3" }

In details:

• The first part {} is the match query, filtering which documents to update (in our case all documents).

• The second part [{ $replaceWith: { ... }] is the update aggregation pipeline (note the squared brackets signifying the use of an aggregation pipeline):

  • With $objectToArray, we first transform the document to an array of key/values such as [{ k: "property1", v: "value1" }, { k: "property2", v: null }, ...].
  • With $filter, we filter this array of key/values by removing items for which v is null.
  • We then transform back the filtered array of key/values to an object using $arrayToObject.
  • Finally, we replace the whole document by the modified one with $replaceWith.

• Don't forget { multi: true }, otherwise only the first matching document will be updated.