Change schema of an object in mongodb with update

Question

I made a csv import from a huge ms excel sheet table. So the collection is one level. I need to change a few column so they are 2 levels.

Example, this is from the csv-import.

{
  title: 'House A',
  energyElectricity: 55,
  energyHeat: 35, 
  energyCooling:45
}

This is not good. I want this in the following format:

  {
    title: 'House A',
    energy: {
     electricity: 55,
     heat: 35,
     cooling:45
    }
  }

Is there anyway to do this with an update query?

I tried some stuff but no luck. Some pseudo code here:

db.consumers.update({}, {energy.electricity: energyElectricity, energy.heat:energyHeat}, {multi:true});

Looks like a duplicate of http://stackoverflow.com/questions/3974985/update-mongodb-field-using-value-of-another-field — TeTeT, May 05 '14 at 20:26

score 0 · Answer 1 · answered May 06 '14 at 03:53

There really is no other way to do this other than looping the results as it is presently not possible to refer to any existing fields of a document during an update operation.

So your basic construct needs to look something like ( in whatever language ):

db.collection.find({}).forEach(function(doc) {
    db.collection.update(
        { "_id": doc._id },
        {
            "title": doc.title,
            "energy": {
               "electricity": doc.energyElectricty,
               "heat": doc.energyHeat,
               "cooling": doc.energyCooling
            }
        }
    );

});

You could do this a little more efficiently with "bulk updates" as available from MongoDB 2.6 and upwards:

var batch = [];
var count = 0;

db.collection.find({}).forEach(function(doc) {

    batch.push({
        "q": { "_id": doc._id },
        "u": {
            "title": doc.title,
            "energy": {
               "electricity": doc.energyElectricty,
               "heat": doc.energyHeat,
               "cooling": doc.energyCooling
            }
        }
    });
    count++;

    if ( count % 500 == 0 ) {
        db.runCommand({ "update": "collection", "updates": batch });
        batch = [];
    }

});

if ( batch.length > 0 ) {
    db.runCommand({ "update": "collection", "updates": batch });
}

So while all updates are still being done over the wire, this does actually only send over the wire once per 500 ( or how many your feel comfortably sits under the 16MB BSON limit ) items.

Of course though, since you mention this came from a CSV import, you can always re-shape your input and import the collection again if that turns out to be a reasonable option.

Change schema of an object in mongodb with update

1 Answers1