Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to change the type of a field?

Tags:

mongodb

The only way to change the $type of the data is to perform an update on the data where the data has the correct type.

In this case, it looks like you're trying to change the $type from 1 (double) to 2 (string).

So simply load the document from the DB, perform the cast (new String(x)) and then save the document again.

If you need to do this programmatically and entirely from the shell, you can use the find(...).forEach(function(x) {}) syntax.


In response to the second comment below. Change the field bad from a number to a string in collection foo.

db.foo.find( { 'bad' : { $type : 1 } } ).forEach( function (x) {   
  x.bad = new String(x.bad); // convert field to string
  db.foo.save(x);
});

Convert String field to Integer:

db.db-name.find({field-name: {$exists: true}}).forEach(function(obj) { 
    obj.field-name = new NumberInt(obj.field-name);
    db.db-name.save(obj);
});

Convert Integer field to String:

db.db-name.find({field-name: {$exists: true}}).forEach(function(obj) {
    obj.field-name = "" + obj.field-name;
    db.db-name.save(obj);
});

Starting Mongo 4.2, db.collection.update() can accept an aggregation pipeline, finally allowing the update of a field based on its own value:

// { a: "45", b: "x" }
// { a:  53,  b: "y" }
db.collection.update(
  { a : { $type: 1 } },
  [{ $set: { a: { $toString: "$a" } } }],
  { multi: true }
)
// { a: "45", b: "x" }
// { a: "53", b: "y" }
  • The first part { a : { $type: 1 } } is the match query:

    • It filters which documents to update.
    • In this case, since we want to convert "a" to string when its value is a double, this matches elements for which "a" is of type 1 (double)).
    • This table provides the codes representing the different possible types.
  • The second part [{ $set: { a: { $toString: "$a" } } }] is the update aggregation pipeline:

    • Note the squared brackets signifying that this update query uses an aggregation pipeline.
    • $set is a new aggregation operator (Mongo 4.2) which in this case modifies a field.
    • This can be simply read as "$set" the value of "a" to "$a" converted "$toString".
    • What's really new here, is being able in Mongo 4.2 to reference the document itself when updating it: the new value for "a" is based on the existing value of "$a".
    • Also note "$toString" which is a new aggregation operator introduced in Mongo 4.0.
  • Don't forget { multi: true }, otherwise only the first matching document will be updated.


In case your cast isn't from double to string, you have the choice between different conversion operators introduced in Mongo 4.0 such as $toBool, $toInt, ...

And if there isn't a dedicated converter for your targeted type, you can replace { $toString: "$a" } with a $convert operation: { $convert: { input: "$a", to: 2 } } where the value for to can be found in this table:

db.collection.update(
  { a : { $type: 1 } },
  [{ $set: { a: { $convert: { input: "$a", to: 2 } } } }],
  { multi: true }
)

For string to int conversion.

db.my_collection.find().forEach( function(obj) {
    obj.my_value= new NumberInt(obj.my_value);
    db.my_collection.save(obj);
});

For string to double conversion.

    obj.my_value= parseInt(obj.my_value, 10);

For float:

    obj.my_value= parseFloat(obj.my_value);