Mongodb aggregation remove null values from object with nested properties

Working with Data
#1

Is there a way to remove literally all null or empty string values from an object? We have an aggregation which creates an object with empty fields and empty objects should the value be null.
What we wish to do is remove all null properties and empty objects and recreate the object, in order to keep the data as small as possible.

e.g. in the following object, only ‘test’ and ‘more-nested-data’ should be taken into account, the rest can be removed


{
    "test": "some",
    "test2": {
    },
    "test3": {
        "some-key": {
        },
        "some-other-key": {
            "more-nested-data": true,
            "more-nested-emtpy": null
        }
    }
}

which should become:

{
    "test": "some",
    "test3": {
        "some-other-key": {
            "more-nested-data": true
        }
    }
}

I tried a lot, but I think by using objectToArray that something could be done, but I have not found the solution yet. The required aggregation should need to recursively (or by defined levels) remove null properties and empty objects.

#2

Hi @Brian_Marting,

If those documents should be retrieved from the database but only the myKey: null should be gone, we can $project off the field using {myKey:0}.

I suspect you tried this but it is not enough? I am not sure whether there is any other way though.

#3

We don’t know what field is null, the thing is, the problem is introduced by the $project aggregation itself.
we have some project mappings e.g.

deptest.trainstation.gate.tunnel: $deptestTrainstationTunnel
deptest.trainstation.gate.passage: $deptestTrainstationPassage
deptest.trainstation.track: $deptestTrainstationTrack

This is all fine, until icao and iata both are null. This will result in the following object:

deptest: {
   trainstation: {
      gate: {},
      track: 4d,
      ...
   }
}

As you can see here, there is one gate object that is just empty, and can be left out. It does a great job of leaving the properties out, but the parent path is still constructed though
The same thing happens in some cases where we convert some strings to dates, should these strings be null, the value will also be null and the property stays present

#4

It’s easy to do what you describe at the top level of the document. It’s a little harder to do it within subdocuments, especially if you don’t know how many levels they may be embedded and/or if some of them might be array of subdocuments.

Note that you can update the documents in place, or you can use the same pipeline in aggregation without modifying original documents.

For top level fields:

db.empty.aggregate([
    {$replaceWith:{
        $arrayToObject:{
            $filter:{
                input:{$objectToArray:"$$ROOT"}, 
                cond:{$not:{$in:["$$this.v", [null, "", {}]  ]}}
            }
        }
    }}
])

Same thing as an update:

db.empty.update(
    {},
    [{$replaceWith:{$arrayToObject:{$filter:{
        input:{$objectToArray:"$$ROOT"}, 
        cond:{$not:{$in:["$$this.v", [null, "", {}]  ]}}
    }}}}],
    {multi:true}
)

It’s a little tricker to do it for subdocuments, but possible using the same approach.

Asya

3 Likes
#5

A post was split to a new topic: How do I match a field which is not empty?

#6