Spring boot mongodb : Updating multiple records with custom value using updateMulti

Question

I have a list of records. Each record needs to have 2 fields: temp(temperature in Celcius) and temp_F(temperature in Fahrenheit). temp field is available in mostly all records. But temp_F is missing in most of the records. I want to populate missing temp_F value in the record using the temp value of the record. Here is what i am doing:

Query updateTempFQuery = Query.query(Criteria.where("temp_F").isNull());
        List<TelemetryReport>reports = mongoTemplate.find(updateTempFQuery,TelemetryReport.class);

        for(TelemetryReport report:reports){
            if(report.getTemp() == null) continue;
            Double fahrenHeitValue = new BigDecimal("32").add(new BigDecimal("1.8").multiply(new BigDecimal(report.getTemp().toString()))).doubleValue();
           // temperature in fahrenheit = 32 + 1.8*(temp in celcius)
            Update applyUpdate = new Update().set("temp_F",fahrenHeitValue);
            mongoTemplate.updateFirst(updateTempFQuery,applyUpdate,TelemetryReport.class);
        }

But the code is throwing timeout error because of huge number of records. I want to do it using updateMulti and aggregation or some other similar methods. But i am unable to find some solution. Please help.

Answer 1

To optimize your operation for updating a large number of records in MongoDB, you can use the updateMulti method combined with an aggregation pipeline. This approach will allow you to perform the update in a more efficient manner, especially when dealing with a huge number of records.

Here's how you can modify your code to use updateMulti:

1. Build the Query: You already have the query to find records where temp_F is null.
2. Create an Update with Aggregation Pipeline: In MongoDB 4.2 and later, you can use an aggregation pipeline for the update operation, which allows you to calculate and set the new value for temp_F directly in the update command.

Here's a sample code snippet:

import org.springframework.data.mongodb.core.query.Criteria;
import org.springframework.data.mongodb.core.query.Query;
import org.springframework.data.mongodb.core.query.Update;
import org.bson.Document;

// Your existing query to find records with null temp_F
Query updateTempFQuery = Query.query(Criteria.where("temp_F").isNull());

// Create the aggregation pipeline for the update
List<Document> updatePipeline = Arrays.asList(
    new Document("$set", new Document("temp_F", 
        new Document("$cond", Arrays.asList(
            new Document("$ne", Arrays.asList("$temp", null)),
            new Document("$add", Arrays.asList(32, new Document("$multiply", Arrays.asList(1.8, "$temp")))),
            "$temp_F"
        ))
    ))
);

// Create a Document representing the update pipeline
Document updatePipelineDoc = new Document("$set", updatePipeline);

// Use Update.fromDocument to create an Update with the pipeline
Update update = Update.fromDocument(updatePipelineDoc);

// Use updateMulti to update all matching documents
mongoTemplate.updateMulti(updateTempFQuery, update, TelemetryReport.class);

In this code:

• The update is created with an aggregation pipeline. The $cond operator is used to check if temp is not null. If temp is not null, it calculates the Fahrenheit value. Otherwise, it keeps the original temp_F.
• The updateMulti method is used to apply this update to all documents matching the query.

This approach should be significantly faster than iterating over each document and updating them one by one, especially for a large dataset.

Ensure that your MongoDB server is version 4.2 or later, as this feature is not available in earlier versions.

Answer 2

You could easily do this with a simple updateMany command.

db.collection.updateMany(
   { "temp_F": { "$exists": false } },
   [
      {
         "$set": {
            "temp_F": {
               "$add": [
                  { "$multiply": [ 1.8, "$temp" ] },
                  32
               ]
            }
         }
      }
   ]
)

With the Java API, the MongoDB command would transform in the following code:

Bson filter = Filters.eq("temp_F", null);
Bson update = Updates.combine(
    Updates.set("temp_F", 
        new Document("$add", Arrays.asList(
            new Document("$multiply", Arrays.asList(1.8, "$temp")),
            32
        ))
    )
);
UpdateResult result = collection.updateMany(filter, update);