How to change the type of a field?
Asked Answered
B

14

203

I am trying to change the type of a field from within the mongo shell.

I am doing this...

db.meta.update(
  {'fields.properties.default': { $type : 1 }}, 
  {'fields.properties.default': { $type : 2 }}
)

But it's not working!

Brittni answered 11/2, 2011 at 19:18 Comment(1)
If anyone is in the same situation I was, having to toString some document's field, here's the little program I've made/used.Morentz
G
229

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);
});
Gyatt answered 11/2, 2011 at 20:21 Comment(11)
Any chance of an example - changing a field type from int to string (or vice versa), from the shell?Mentalist
in case Int32->String, new String(x.bad) creates collection of Strings with 0-index-item x.bad value. Variant ""+x.bad, described by Simone works as desired - creates String value instead of Int32Leonhard
The above code is converting the field data from double to array instead of double to string. My actual data was in this format :3.1 whereas simone code is working fine for meRumple
Had a situation where I needed to convert the _id field as well as not conflict with other indexes: db.questions.find({_id:{$type:16}}).forEach( function (x) { db.questions.remove({_id:x._id},true); x._id = ""+x._id; db.questions.save(x); });Ardell
@SundarBons yes you are re-writing a field across your database, this is a big deal no matter how you do it. If you were using SQL and this was a big table you would probably have to take some down time.Gyatt
already use this, it will stop without completing all, I will not recommend thisTutankhamen
IMO this should not be the accepted answer. The answer of Simone is the one that works. If you use new String(obj.field) you will get a dict and mess up your mongo collection.Supra
Sadly, Simone's answer came about 6 months after mine. The only person who can change the "checkmark" is the original asker. FWIW, they both use the same basic syntax, with Simone's addressing two very specific cases. I would not use any of these methods on a Production database with any amount of real data.Gyatt
This works but damn! SOOOO SLOOOOOW! MongoDB needs to up its game with these kind of update operations. In SQL I could do an update that references values from the same row for thousands of records in only a few ms, like UPDATE sometable SET name = CONCAT(first, ' ', last) ... but in Mongo it's about the same time ... per record.Niobe
Yes, @ClubbedAce, it is very slow. You are effectively loading every entry in the collection and then re-saving this. I know it looks like you could do this quickly in SQL, but if you had a 3TB table, the change you list would likely lock the table and would also take a long a time. To be clear, I would never use this method in a production database with any significant amount of data. If this data is actively being used, you need to migrate the data to a new field and update code, etc. Data safety is a lot of work.Gyatt
.save is not a function facing this errorMegathere
M
173

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);
});
Mangrum answered 17/10, 2011 at 15:45 Comment(7)
This is great - do you know how you'd convert a string (think currency like '1.23') to the integer 123? I assume you'd have to parse it as a float or decimal, multiply it by 100, then save it as an integer, but I can't find the right docs to do this. Thanks!Tame
Actually this working is good. But I have an application running with mongoid 2.4.0-stable which has fields such as field: customer_count, type: Integer & a validation as validates_numericality_of :customer_count which was working fine. Now when I am upgrading to mongoid to 3.0.16, when I assign a string value it automatically converts it to 0. without an error. I want to throw an error on wrong data assignment, this behavior turning out strange for me.Schulein
I ran this and got the error: Error: could not convert string to integer (shell):1Kamchatka
And if you need to convert string (or "normal" 32-bit integer) to 64-bit integer, use NumberLong as here: db.db-name.find({field-name : {$exists : true}}).forEach( function(obj) { obj.field-name = new NumberLong(obj.field-name); db.db-name.save(obj); } );Fregoso
This worked for me not only for numbers but for ObjectId data types too.Cohleen
Hello simone, I have tried this but it is not working for me. I am using mongodb 3.4 in linux 14 system.Gunny
"Convert Integer field to String:" works also for Double conversion to StringElectrify
N
105

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.updateMany(
  { a : { $type: 1 } },
  [{ $set: { a: { $toString: "$a" } } }]
)
// { 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 code 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.

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.updateMany(
  { a : { $type: 1 } },
  [{ $set: { a: { $convert: { input: "$a", to: 2 } } } }]
)
Norinenorita answered 12/6, 2019 at 20:43 Comment(5)
db.collection.updateMany( { a : { $type: 1 } }, [{ $set: { a: { $toString: "$a" } } }] ) - the multi : true can be avoided using updateManyHoelscher
As of 2020, using $convert should be the correct method for this as it should be a lot more efficient (and easier to use to boot).Chape
As of Mongo 4.0 there are shorthand operators: "...In addition to $convert, MongoDB provides the following aggregation operators as shorthand when the default "onError" and "onNull" behavior is acceptable". mongodb.com/docs/manual/reference/operator/aggregation/convert/…Epistemic
This converted a double value of 6503856832666 to "6.50386e+12" :(Rhynchocephalian
@ShashankAgrawal to convert a large double value to a string, avoiding scientific notation, you first have to convert it to a LongKroeger
P
47

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);
Playwright answered 17/3, 2012 at 0:35 Comment(3)
I would recommend also specifying the radix - developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/…Frissell
Watch out, I tested this with Robomongo, and this resulted in type 1: double. Had to use new NumberInt()Plutonic
Daniel, ur solution is not okay... david's solution is okBorrego
D
29
db.coll.find().forEach(function(data) {
    db.coll.update({_id:data._id},{$set:{myfield:parseInt(data.myfield)}});
})
Dickens answered 12/4, 2012 at 13:1 Comment(0)
K
21

all answers so far use some version of forEach, iterating over all collection elements client-side.

However, you could use MongoDB's server-side processing by using aggregate pipeline and $out stage as :

the $out stage atomically replaces the existing collection with the new results collection.

example:

db.documents.aggregate([
         {
            $project: {
               _id: 1,
               numberField: { $substr: ['$numberField', 0, -1] },
               otherField: 1,
               differentField: 1,
               anotherfield: 1,
               needolistAllFieldsHere: 1
            },
         },
         {
            $out: 'documents',
         },
      ]);
Krug answered 3/9, 2018 at 12:39 Comment(1)
I don't know why this isn't upvoted more. Row by row operations on large data sets are murder on performancePertussis
P
12

To convert a field of string type to date field, you would need to iterate the cursor returned by the find() method using the forEach() method, within the loop convert the field to a Date object and then update the field using the $set operator.

Take advantage of using the Bulk API for bulk updates which offer better performance as you will be sending the operations to the server in batches of say 1000 which gives you a better performance as you are not sending every request to the server, just once in every 1000 requests.

The following demonstrates this approach, the first example uses the Bulk API available in MongoDB versions >= 2.6 and < 3.2. It updates all the documents in the collection by changing all the created_at fields to date fields:

var bulk = db.collection.initializeUnorderedBulkOp(),
    counter = 0;

db.collection.find({"created_at": {"$exists": true, "$type": 2 }}).forEach(function (doc) {
    var newDate = new Date(doc.created_at);
    bulk.find({ "_id": doc._id }).updateOne({ 
        "$set": { "created_at": newDate}
    });

    counter++;
    if (counter % 1000 == 0) {
        bulk.execute(); // Execute per 1000 operations and re-initialize every 1000 update statements
        bulk = db.collection.initializeUnorderedBulkOp();
    }
})
// Clean up remaining operations in queue
if (counter % 1000 != 0) { bulk.execute(); }

The next example applies to the new MongoDB version 3.2 which has since deprecated the Bulk API and provided a newer set of apis using bulkWrite():

var bulkOps = [];

db.collection.find({"created_at": {"$exists": true, "$type": 2 }}).forEach(function (doc) { 
    var newDate = new Date(doc.created_at);
    bulkOps.push(         
        { 
            "updateOne": { 
                "filter": { "_id": doc._id } ,              
                "update": { "$set": { "created_at": newDate } } 
            }         
        }           
    );     
})

db.collection.bulkWrite(bulkOps, { "ordered": true });
Picklock answered 22/1, 2016 at 11:44 Comment(1)
Great answer, the bulk method runs about 100x quicker for me even though it still appears to be a synchronous call.Pentacle
S
3

To convert int32 to string in mongo without creating an array just add "" to your number :-)

db.foo.find( { 'mynum' : { $type : 16 } } ).forEach( function (x) {   
  x.mynum = x.mynum + ""; // convert int32 to string
  db.foo.save(x);
});
Sjoberg answered 3/10, 2014 at 14:19 Comment(0)
F
3

What really helped me to change the type of the object in MondoDB was just this simple line, perhaps mentioned before here...:

db.Users.find({age: {$exists: true}}).forEach(function(obj) {
    obj.age = new NumberInt(obj.age);
    db.Users.save(obj);
});

Users are my collection and age is the object which had a string instead of an integer (int32).

Floodlight answered 28/3, 2017 at 5:46 Comment(0)
T
2
You can easily convert the string data type to numerical data type.
Don't forget to change collectionName & FieldName.
for ex : CollectionNmae : Users & FieldName : Contactno.

Try this query..

db.collectionName.find().forEach( function (x) {
x.FieldName = parseInt(x.FieldName);
db.collectionName.save(x);
});
Toile answered 25/1, 2017 at 13:40 Comment(0)
A
1

I need to change datatype of multiple fields in the collection, so I used the following to make multiple data type changes in the collection of documents. Answer to an old question but may be helpful for others.

db.mycoll.find().forEach(function(obj) { 

    if (obj.hasOwnProperty('phone')) {
        obj.phone = "" + obj.phone;  // int or longint to string
    }

    if (obj.hasOwnProperty('field-name')) {
     obj.field-name = new NumberInt(obj.field-name); //string to integer
    }

    if (obj.hasOwnProperty('cdate')) {
        obj.cdate = new ISODate(obj.cdate); //string to Date
    }

    db.mycoll.save(obj); 
});
Azedarach answered 20/1, 2016 at 9:55 Comment(0)
G
1

demo change type of field mid from string to mongo objectId using mongoose

 Post.find({}, {mid: 1,_id:1}).exec(function (err, doc) {
             doc.map((item, key) => {
                Post.findByIdAndUpdate({_id:item._id},{$set:{mid: mongoose.Types.ObjectId(item.mid)}}).exec((err,res)=>{
                    if(err) throw err;
                    reply(res);
                });
            });
        });

Mongo ObjectId is just another example of such styles as

Number, string, boolean that hope the answer will help someone else.

Grassplot answered 26/8, 2017 at 3:40 Comment(0)
R
1

The above answers almost worked but had a few challenges-

Problem 1: db.collection.save no longer works in MongoDB 5.x

For this, I used replaceOne().

Problem 2: new String(x.bad) was giving exponential number

I used "" + x.bad as suggested above.

My version:

let count = 0;

db.user
    .find({
        custID: {$type: 1},
    })
    .forEach(function (record) {
        count++;

        const actualValue = record.custID;
        record.custID = "" + record.custID;
        console.log(`${count}. Updating User(id:${record._id}) from old id [${actualValue}](${typeof actualValue}) to [${record.custID}](${typeof record.custID})`)

        db.user.replaceOne({_id: record._id}, record);
    });

And for millions of records, here are the output (for future investigation/reference)-

enter image description here

Rhynchocephalian answered 28/12, 2022 at 6:14 Comment(0)
I
0

I use this script in mongodb console for string to float conversions...

db.documents.find({ 'fwtweaeeba' : {$exists : true}}).forEach( function(obj) { 
        obj.fwtweaeeba = parseFloat( obj.fwtweaeeba ); 
        db.documents.save(obj); } );    

db.documents.find({ 'versions.0.content.fwtweaeeba' : {$exists : true}}).forEach( function(obj) { 
        obj.versions[0].content.fwtweaeeba = parseFloat( obj.versions[0].content.fwtweaeeba ); 
        db.documents.save(obj); } );

db.documents.find({ 'versions.1.content.fwtweaeeba' : {$exists : true}}).forEach( function(obj) { 
        obj.versions[1].content.fwtweaeeba = parseFloat( obj.versions[1].content.fwtweaeeba );  
        db.documents.save(obj); } );

db.documents.find({ 'versions.2.content.fwtweaeeba' : {$exists : true}}).forEach( function(obj) { 
        obj.versions[2].content.fwtweaeeba = parseFloat( obj.versions[2].content.fwtweaeeba );  
        db.documents.save(obj); } );

And this one in php)))

foreach($db->documents->find(array("type" => "chair")) as $document){
    $db->documents->update(
        array('_id' => $document[_id]),
        array(
            '$set' => array(
                'versions.0.content.axdducvoxb' => (float)$document['versions'][0]['content']['axdducvoxb'],
                'versions.1.content.axdducvoxb' => (float)$document['versions'][1]['content']['axdducvoxb'],
                'versions.2.content.axdducvoxb' => (float)$document['versions'][2]['content']['axdducvoxb'],
                'axdducvoxb' => (float)$document['axdducvoxb']
            )
        ),
        array('$multi' => true)
    );


}
Infuriate answered 3/4, 2014 at 9:25 Comment(0)

© 2022 - 2024 — McMap. All rights reserved.