How to convert the type for a number of fields in all documents in a collection?

Question

After a mongoimport from CSV I ended up with a large number of boolean fields stored as strings. The string values are all "TRUE", "FALSE" or empty.

How can I convert these fields to proper booleans, for the entire collection?

possible duplicate of [How do I convert a property in MongoDB from text to date type?](http://stackoverflow.com/questions/2900674/how-do-i-convert-a-property-in-mongodb-from-text-to-date-type) — WiredPrairie, Dec 30 '13 at 12:00
That question is similar, but deals with a single field only. Also, it doesn't query by field values. The answer is nice and pretty, though :) — Per Christian Henden, Dec 30 '13 at 13:08
I would have thought you could have extrapolated from that technique the way to handle multiple fields. Your comments seem to imply that you want someone else to write the complete solution for you. All you need to do is loop through the collection and make the changes you want. — WiredPrairie, Dec 30 '13 at 13:11
I see your point! That task feels a bit daunting to me, as I'm new to mongodb. In principle, I agree. Ideally, there would be a different question for generating queries in a loop and the actual update of the field. — Per Christian Henden, Dec 30 '13 at 13:30
OK, I've thrown together a simple example you could use from the shell. It loops through all fields (not of sub documents), and modifies them if they are `"TRUE"` or `"FALSE"`. — WiredPrairie, Dec 30 '13 at 13:41
@WiredPrairie Also, I feel I must mention that I did post an answer together with my question. Just to help others out with the same problem I had struggled with :) Your solution is better, though! — Per Christian Henden, Dec 30 '13 at 13:57

score 0 · Answer 1 · answered Dec 30 '13 at 05:43

0

in mogodb shell you can use following statement

db.collection.find({"Fieldname":"TRUE"}).forEach(function(a){a.Fieldname=true; db.collection.save(a)})

db.collection.find({"Fieldname":"False"}).forEach(function(a){a.Fieldname=false; db.collection.save(a)})

this way you can change it from string to Boolean.

answered Dec 30 '13 at 05:43

nish71

275
1
7

That works. There's a large number of different fieldnames, so I wanted to do it in a loop. – Per Christian Henden Dec 30 '13 at 11:28

WiredPrairie · Accepted Answer · 2013-12-30T14:16:54.433

0

While you can do something very efficient with $set possibly (although you're changing the nature of the size of data making strings into booleans), I'd go simple in this case.

From the MongoDB console, you could use code like this:

db.myCollection.find().forEach(function(doc) { 
      var changed = false;
      // loop through all fields, looking for "TRUE" or "FALSE"
      for(var field in doc) {
         var value = doc[field];
         if (value === "TRUE") { 
             doc[field] = true; 
             changed = true;
         } else if (value === "FALSE") {
             doc[field] = false;
             changed = true;
         } else if (value === "" || value === null ) {
             // remove empty
             delete doc[field];
             changed = true;
         }
      }
      // continue pattern checking more fields if needed

      if (changed) {    // if something changed, update the entire document
          db.myCollection.update({_id: doc._id}, doc);
      }
});

edited Dec 30 '13 at 14:16

answered Dec 30 '13 at 13:40

WiredPrairie

58,954
17
116
143

Thanks! It should probably loop through a list of specific fields, so that it can replace empty strings with null. Otherwise the type is not consistent across documents (sometimes boolean, sometimes string). The looping can be lifted from answer http://stackoverflow.com/a/20831477/788913 – Per Christian Henden Dec 30 '13 at 14:00
You can do it however you'd like. I just wanted to show that there was a way to check multiple fields at one time conveniently for a consistent pattern. I added a check for `null`/`""` and deleted that field. – WiredPrairie Dec 30 '13 at 14:15
You wouldn't want to find each field manually though as suggested in your answer. I'd suggest it's much better to loop through all documents and just fix each document, all at once. – WiredPrairie Dec 30 '13 at 14:20

Per Christian Henden · Answer 3 · 2013-12-30T11:30:49.443

Loop over the different fields that should be converted, and generate a query that contains the current key and the value that should be converted. For each document that matches the query, replace the string value with a boolean value.

var boolean_fields = ['Key_a1','Key_a2', ..,'Key_an'];
var queryFunction = function(key, value){
    var expression = {};
    expression[key] = value;
    return expression;
}

boolean_fields.forEach(function(fieldname, index, array) {

    db.products.find( queryFunction(fieldname, "TRUE") ).forEach( function (match) {
      match[fieldname] = true; // convert field to boolean
      db.products.save(match);
    });

    db.products.find( queryFunction(fieldname, "FALSE") ).forEach( function (match) {
      match[fieldname] = false; // convert field to boolean
      db.products.save(match);
    });

    db.products.find( queryFunction(fieldname, "") ).forEach( function (match) {
      match[fieldname] = null; // remove string type
      db.products.save(match);
    });    

});

Why don't you just form one big $set instead of doing the fields individually? — Sammaye, Dec 30 '13 at 00:05
@Sammaye Not familiar with the $set construct, but yes, it seems like that could shave off the inner forEach and save. Thanks for pointing that out! — Per Christian Henden, Dec 30 '13 at 00:28

How to convert the type for a number of fields in all documents in a collection?

3 Answers3