HaBo HaBo - 4 months ago 67
Bash Question

MongoDB Shell Script Update all field Names where there is space in field name

Using

MongoDB
shell script 3.2, how can I update all fields where field names have a
space replace those with underscore
?

{
"Some Field": "value",
"OtherField" :"Value",
"Another Field" : "Value"
}


update the above document as below

{
"Some_Field": "value",
"OtherField" :"Value",
"Another_Field" : "Value"
}


rename field can be done with something like this

db.CollectionName.update( { _id: 1 }, { $rename: { 'nickname': 'alias', 'cell': 'mobile' } } )


Challenging part here is
filter
, how to come up with a
filter
where there is a space in field name

Answer

This needs a two-step approach. First, you need a mechanism to get a lst of all the keys with a space in your collection. Once you get the list, construct an object that maps those keys to their renamed values. You can then use that object as your $rename operator document. Consider using mapReduce to get the list of keys with spaces.

The following mapReduce operation will populate a separate collection with all the filtered keys as the _id values:

mr = db.runCommand({
    "mapreduce": "CollectionName",
    "map": function() {
        var regxp = /\s/;
        for (var key in this) { 
            if (key.match(r)) {
                emit(key, null); 
            }
        }
    },
    "reduce": function() {}, 
    "out": "filtered_keys"
})

To get a list of all the dynamic keys, run distinct on the resulting collection:

db[mr.result].distinct("_id")
["Some Field", "Another Field"]

Now given the list above, you can assemble your update document by creating an object that will have its properties set within a loop. Normally your update document will have this structure:

var update = {
    "$rename": {
        "Some Field": "Some_Field",
        "Another Field": "Another_Field"        
    }
}

Thus

var update = { "$rename": {} };
db[mr.result].distinct("_id").forEach(function (key){
    update["$rename"][key] = key.replace(/ /g,"_");
});

which you can then use in your update as

db.collection.update({ }, { "$rename": update }, false, true );