MongoDB query to update each field of documents in collection with a formula?

To update each field of documents in collection with a formula in MongoDB, use the $mul operator with the positional all operator $[] to apply mathematical operations across all array elements.

Syntax

db.collection.update(
    {},
    { $mul: { "arrayField.$[].fieldName": formulaValue } },
    { multi: true }
);

Sample Data

db.demo749.insertOne({
    "details": [
        {"id": 1, "a": 10},
        {"id": 2, "a": 5},
        {"id": 3, "a": 20}
    ]
});

{
    "acknowledged": true,
    "insertedId": ObjectId("5eae6fb0a930c785c834e565")
}

Verify Initial Data

db.demo749.find().pretty();

{
    "_id": ObjectId("5eae6fb0a930c785c834e565"),
    "details": [
        {
            "id": 1,
            "a": 10
        },
        {
            "id": 2,
            "a": 5
        },
        {
            "id": 3,
            "a": 20
        }
    ]
}

Update with Formula

Apply the formula a * (2/5) to multiply each "a" field by 0.4 ?

db.demo749.update(
    {},
    { $mul: { "details.$[].a": 2/5 } },
    { multi: true }
);

WriteResult({ "nMatched": 1, "nUpserted": 0, "nModified": 1 })

Verify Updated Results

db.demo749.find().pretty();

{
    "_id": ObjectId("5eae6fb0a930c785c834e565"),
    "details": [
        {
            "id": 1,
            "a": 4
        },
        {
            "id": 2,
            "a": 2
        },
        {
            "id": 3,
            "a": 8
        }
    ]
}

Key Points

• $[] targets all elements in the array without needing specific conditions.
• $mul performs multiplication operations on numeric fields.
• Use multi: true to update all matching documents in the collection.

Conclusion

The $mul operator combined with $[] efficiently applies mathematical formulas to all array elements. This approach scales well for bulk updates across collections with nested numeric data.