Replace string value with float value

Question

My data structure looks like this:

{
    customer: 10000001,
    metadata: {
        foo: '10.4',
        bar: 99
    }
}

Unfortunately there is a mistake in my dataset as all values for 'metadata.foo' have been stored as a string. There are 500 affected documents (the value is optional), so it is not possible to correct the values manually.

Is it possible to replace the string value with its float value if it is existing at all by terminal without executing some JavaScript code?

I'm thinking of something like:

db.collection.update(
    {
        'metadata.foo': { $exists: true }
    },
    {
        $toDecimal: <expression??>
    }
)

Valijon · Accepted Answer · 2020-02-27 19:22:11Z

1

Try this:

db.collection.update(
    {
        'metadata.foo': { $exists: true, $type:"string" }
    },
    [{
        $set:{"metadata.foo" : {$toDouble:"$metadata.foo"}}
    }],
    {multi:true}
)

I assume you have MongoDB v4.2

For MongoDB earlier v4.2, MongoDB doesn't allow override values by reading from itself.

Walkaround #1:

db.collection.aggregate([
    {
        $match:{'metadata.foo': { $exists: true, $type:"string" }}
    },
    {
        $addFields:{"metadata.foo" : {$toDouble:"$metadata.foo"}}
    }
]).forEach(function(doc){
    db.collection.save(doc);    
})

Walkaround #2: Replace , to .:

db.collection.find({
    $match:{'metadata.foo': { $exists: true, $type:"string" }}
}).forEach(function(doc){
    doc.metadata.foo = parseFloat(doc.metadata.foo.replace(",","."));
    db.collection.save(doc);    
})

edited Feb 27, 2020 at 19:22

answered Feb 27, 2020 at 18:49

Valijon

13.2k4 gold badges40 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

user3142695 Over a year ago

I'm using v4.0.

user3142695 Over a year ago

Only one problem: How can I handle string values like '10,5' which should be handled as 10.5?

Valijon Over a year ago

@user3142695 give me a sec

user3142695 Over a year ago

But your workaround can't be used directly in the terminal, right? So I think I will update to v4.2

Valijon Over a year ago

@user3142695 Yes you can :). Tell me how are you going to execute update query?

|

Collectives™ on Stack Overflow

Replace string value with float value

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related