SQL UPDATE for strings in MongoDB
MongoDB currently does not offer the equivalent of a SQL Update statement when you want to change the value of a string to something that is based on the current value. In my case I needed to find spaces and change them to underscores -- something done easily in php with str_replace(), and in MySQL with REPLACE.
If you had a table named foo with a column named "mystr" you could do this sort of update like this:
UPDATE foo SET mystr = REPLACE(mystr, ' ', '_');
MongoDB does have collection.update() that works with a series of "modifier" operations, but none of them are useful for doing modifications to the current value of a document field when that field is a string.
MongoDB does however support javascript, and the javascript replace() function implements a regular expression search and replace. I was able to accomplish the update using the forEach() method to process the resulting documents one at a time. While not as efficient as having mongo do this internally with a specially built update modifier, at least this method only needs to operate on documents that are known to require modifications, and could be run in the mongo interactive client easily.
Again, assuming the collection is named "foo" and the document field is named "mystr"
db.foo.find({ mystr: /[ ]+/ }).forEach( function(u) { u.mystr = u.mystr.replace(/[ ]/g, "_"); db.foo.save(u); } );
An existing patch promises to add a new update() modifier but until such a time as it exists, this approach can be used when you need to update strings in a collection, referencing the existing values in the transformation.
If you had a table named foo with a column named "mystr" you could do this sort of update like this:
UPDATE foo SET mystr = REPLACE(mystr, ' ', '_');
MongoDB does have collection.update() that works with a series of "modifier" operations, but none of them are useful for doing modifications to the current value of a document field when that field is a string.
MongoDB does however support javascript, and the javascript replace() function implements a regular expression search and replace. I was able to accomplish the update using the forEach() method to process the resulting documents one at a time. While not as efficient as having mongo do this internally with a specially built update modifier, at least this method only needs to operate on documents that are known to require modifications, and could be run in the mongo interactive client easily.
Again, assuming the collection is named "foo" and the document field is named "mystr"
db.foo.find({ mystr: /[ ]+/ }).forEach( function(u) { u.mystr = u.mystr.replace(/[ ]/g, "_"); db.foo.save(u); } );
An existing patch promises to add a new update() modifier but until such a time as it exists, this approach can be used when you need to update strings in a collection, referencing the existing values in the transformation.
Trackbacks
No Trackbacks
Comments
Display comments as Linear | Threaded