I've got a collection consisting of millions of documents that resemble the following:
{
_id: ObjectId('...'),
value: "0.53"
combo: [
{
h: 0,
v: "0.42"
},
{
h: 1,
v: "1.32"
}
]
}
The problem is that the values are stored as strings and I need to convert them to float/double.
I'm trying this and it's working but this'll take days to complete, given the volume of data:
db.collection.find({}).forEach(function(obj) {
if (typeof(obj.value) === "string") {
obj.value = parseFloat(obj.value);
db.collection.save(obj);
}
obj.combo.forEach(function(hv){
if (typeof(hv.value) === "string") {
hv.value = parseFloat(hv.value);
db.collection.save(obj);
}
});
});
I came across bulk update reading the Mongo docs and I'm trying this:
var bulk = db.collection.initializeUnorderedBulkOp();
bulk.find({}).update(
{
$set: {
"value": parseFloat("value"),
}
});
bulk.execute();
This runs... but I get a NAN as a value, which is because it thinks I'm trying to convert "value" to a float. I've tried different variations like this.value and "$value" but to no avail. Plus this approach only attempts to correct the value in the other object, not the ones in the array.
I'd appreciate any help. Thanks in advance!
Figured it out the following way:
1) To convert at the document level, I came across this post and the reply by Markus paved the way to my solution:
var bulk = db.collection.initializeUnorderedBulkOp()
var myDocs = db.collection.find()
var ops = 0
myDocs.forEach(
function(myDoc) {
bulk.find({ _id: myDoc._id }).updateOne(
{
$set : {
"value": parseFloat(myDoc.value),
}
}
);
if ((++ops % 1000) === 0){
bulk.execute();
bulk = db.collection.initializeUnorderedBulkOp();
}
}
)
bulk.execute();
2) The second part involved updating the array object values and I discovered the syntax to do so in the accepted answer on this post. In my case, I knew that there were 24 values in I ran this separately from the first query and the result looked like:
var bulk = db.collection.initializeUnorderedBulkOp()
var myDocs = db.collection.find()
var ops = 0
myDocs.forEach(
function(myDoc) {
bulk.find({ _id: myDoc._id }).update(
{
$set : {
"combo.0.v": parseFloat(myDoc.combo[0].v),
"combo.1.v": parseFloat(myDoc.combo[1].v),
"combo.2.v": parseFloat(myDoc.combo[2].v),
"combo.3.v": parseFloat(myDoc.combo[3].v),
"combo.4.v": parseFloat(myDoc.combo[4].v),
"combo.5.v": parseFloat(myDoc.combo[5].v),
"combo.6.v": parseFloat(myDoc.combo[6].v),
"combo.7.v": parseFloat(myDoc.combo[7].v),
"combo.8.v": parseFloat(myDoc.combo[8].v),
"combo.9.v": parseFloat(myDoc.combo[9].v),
"combo.10.v": parseFloat(myDoc.combo[10].v),
"combo.11.v": parseFloat(myDoc.combo[11].v),
"combo.12.v": parseFloat(myDoc.combo[12].v),
"combo.13.v": parseFloat(myDoc.combo[13].v),
"combo.14.v": parseFloat(myDoc.combo[14].v),
"combo.15.v": parseFloat(myDoc.combo[15].v),
"combo.16.v": parseFloat(myDoc.combo[16].v),
"combo.17.v": parseFloat(myDoc.combo[17].v),
"combo.18.v": parseFloat(myDoc.combo[18].v),
"combo.19.v": parseFloat(myDoc.combo[19].v),
"combo.20.v": parseFloat(myDoc.combo[20].v),
"combo.21.v": parseFloat(myDoc.combo[21].v),
"combo.22.v": parseFloat(myDoc.combo[22].v),
"combo.23.v": parseFloat(myDoc.combo[23].v)
}
}
);
if ((++ops % 1000) === 0){
bulk.execute();
bulk = db.collection.initializeUnorderedBulkOp();
}
}
)
bulk.execute();
Just to give an idea regarding performance, the forEach was going through around 900 documents a minute, which for 15 million records would have taken days, literally! Not only that but this was only converting the types at the document level, not the array level. For that, I would have to loop through each document and loop through each array (15 million x 24 iterations)! With this approach (running both queries side by side), it completed both in under 6 hours.
I hope this helps someone else.