MongoDb - Change type from Int to Double

19,215

By default all "numbers" are stored as "double" in MongoDB unless generally cast overwise.

Take the following samples:

db.sample.insert({ "a": 1 })
db.sample.insert({ "a": NumberLong(1) })
db.sample.insert({ "a": NumberInt(1) })
db.sample.insert({ "a": 1.223 })

This yields a collection like this:

{ "_id" : ObjectId("559bb1b4a23c8a3da73e0f76"), "a" : 1 }
{ "_id" : ObjectId("559bb1bba23c8a3da73e0f77"), "a" : NumberLong(1) }
{ "_id" : ObjectId("559bb29aa23c8a3da73e0f79"), "a" : 1 }
{ "_id" : ObjectId("559bb30fa23c8a3da73e0f7a"), "a" : 1.223 }

Despite the different constructor functions note how several of the data points there look much the same. The MongoDB shell itself doesn't always clearly distinquish between them, but there is a way you can tell.

There is of course the $type query operator, which allows selection of BSON Types.

So testing this with Type 1 - Which is "double":

> db.sample.find({ "a": { "$type": 1 } })
{ "_id" : ObjectId("559bb1b4a23c8a3da73e0f76"), "a" : 1 }
{ "_id" : ObjectId("559bb30fa23c8a3da73e0f7a"), "a" : 1.223 }

You see that both the first insert and the last are selected, but of course not the other two.

So now test for BSON Type 16 - which is a 32-bit integer

> db.sample.find({ "a": { "$type": 16 } })
{ "_id" : ObjectId("559bb29aa23c8a3da73e0f79"), "a" : 1 }

That was the "third" insertion which used the NumberInt() function in the shell. So that function and other serialization from your driver can set this specific BSON type.

And for the BSON Type 18 - which is 64-bit integer

> db.sample.find({ "a": { "$type": 18 } })
{ "_id" : ObjectId("559bb1bba23c8a3da73e0f77"), "a" : NumberLong(1) }

The "second" insertion which was contructed via NumberLong().

If you wanted to "weed out" things that were "not a double" then you would do:

db.sample.find({ "$or": [{ "a": { "$type": 16 } },{ "a": { "$type": 18 } }]})

Which are the only other valid numeric types other than "double" itself.

So to "convert" these in your collection, you can "Bulk" process like this:

var bulk = db.sample.initializeUnorderedBulkOp(),
    count = 0;
db.sample.find({ 
    "$or": [
        { "a": { "$type": 16 } },
        { "a": { "$type": 18 } }
    ]
}).forEach(function(doc) {
    bulk.find({ "_id": doc._id })
        .updateOne({ 
            "$set": { "b": doc.a.valueOf() } ,
            "$unset": { "a": 1 } 
        });
    bulk.find({ "_id": doc._id })
        .updateOne({ "$rename": { "b": "a" } });
    count++;
    if ( count % 1000 == 0 ) {
        bulk.execute()
        bulk = db.sample.initializeUnOrderedBulkOp();
    }
})
if ( count % 1000 != 0 ) bulk.execute();

What that does is performed in three steps "in bulk":

  1. Re-cast the value to a new field as a "double"
  2. Remove the old field with the unwanted type
  3. Rename the new field to the old field name

This is necessary since the BSON type information is "sticky" to the field element once created. So in order to "re-cast" you need to completely remove the old data which includes the original field assignment.

So that should explain how to "detect" and also "re-cast" unwanted types in your documents.

Share:
19,215

Related videos on Youtube

Carra
Author by

Carra

Updated on September 15, 2022

Comments

  • Carra
    Carra about 1 year

    We have a collection that looks like this:

    {
        "_id" : "10571:6",
        "v" : 261355,
        "ts" : 4.88387e+008
    }
    

    Now, some of the "v" are ints, some are doubles. I want to change them all to doubles.

    I've tried a few things but nothing works (v is an int32 for this record, I want to change it to a double):

    db.getCollection('VehicleLastValues')
    .find
    (
    
        {_id : "10572:6"}
    )
    .forEach
    (
    function (x)
    {
        temp = x.v * 1.0;
        db.getCollection('VehicleLastValues').save(x);
    }}
    

    Things I've tried:

    x.v = x.v * 1.1 / 1.1;
    x.v = parseFloat (new String(x.v));
    

    But I can't get it to be saved as a double...

  • Carra
    Carra over 8 years
    Thanks! I'll give it a try. I was thinking to delete + insert my document but I was hoping that there was an easier way.
  • Blakes Seven
    Blakes Seven over 8 years
    @Carra faster just to move and rename the fields. Especially using the Bulk operations as shown as requests will be sent in batches at most with singular reponses. Deleting documents is bound to cause re-allocation and slow down on I/O
  • jean
    jean over 7 years
    The second initializeUnorderedBulkOp is error typed
  • gorros
    gorros almost 2 years
    For me db.sample.insert({ "a": 1 }) results in number not double.