MongoDB query to convert a string with commas to double

To convert a string with commas to a double value in MongoDB, use the aggregate pipeline with $split, $reduce, and $convert operators to remove commas and convert the result to a numeric type.

Syntax

db.collection.aggregate([
    {
        $project: {
            convertedField: {
                $convert: {
                    input: {
                        $reduce: {
                            input: { $split: ["$stringField", ","] },
                            initialValue: "",
                            in: { $concat: ["$$value", "$$this"] }
                        }
                    },
                    to: "double",
                    onError: 0
                }
            }
        }
    }
]);

Sample Data

db.demo335.insertMany([
    { "Value": "45,67,78.0" },
    { "Value": "17664,76,534.0" },
    { "Value": "8899,322,135,875.50" },
    { "Value": "1,533.07" }
]);

{
    "acknowledged": true,
    "insertedIds": [
        ObjectId("5e522a1cf8647eb59e562091"),
        ObjectId("5e522a26f8647eb59e562092"),
        ObjectId("5e522a34f8647eb59e562093"),
        ObjectId("5e522ab9f8647eb59e562094")
    ]
}

Display all documents from the collection ?

db.demo335.find();

{ "_id": ObjectId("5e522a1cf8647eb59e562091"), "Value": "45,67,78.0" }
{ "_id": ObjectId("5e522a26f8647eb59e562092"), "Value": "17664,76,534.0" }
{ "_id": ObjectId("5e522a34f8647eb59e562093"), "Value": "8899,322,135,875.50" }
{ "_id": ObjectId("5e522ab9f8647eb59e562094"), "Value": "1,533.07" }

Example: Convert String with Commas to Double

db.demo335.aggregate([
    {
        $project: {
            data: {
                $convert: {
                    input: {
                        $reduce: {
                            input: { $split: ["$Value", ","] },
                            initialValue: "",
                            in: { $concat: ["$$value", "$$this"] }
                        }
                    },
                    to: "double",
                    onError: 0
                }
            }
        }
    }
]);

{ "_id": ObjectId("5e522a1cf8647eb59e562091"), "data": 456778 }
{ "_id": ObjectId("5e522a26f8647eb59e562092"), "data": 1766476534 }
{ "_id": ObjectId("5e522a34f8647eb59e562093"), "data": 8899322135875.5 }
{ "_id": ObjectId("5e522ab9f8647eb59e562094"), "data": 1533.07 }

How It Works

• $split divides the string by commas into an array of substrings
• $reduce concatenates all array elements back into a single string without commas
• $convert transforms the comma-free string into a double value
• onError: 0 handles conversion failures by returning 0

Conclusion

Use the combination of $split, $reduce, and $convert operators in an aggregation pipeline to remove commas from numeric strings and convert them to double values in MongoDB.