Update all documents setting a value equal to value of an array where index taken from another value (MongoDB)

user_413843 0
Viewed 47

I am trying to execute a mongo Shell command on a 80k documents Collection in MongoDB.

Here is a sample doc:

{ 
   "weights":[ 
      { 
         "amount":100,
         "description":"grams",
         "grams":100
      },
      { 
         "amount":1,
         "description":"cup",
         "grams":258
      },
      { 
         "amount":1,
         "description":"tbsp",
         "grams":16
      },
      { 
         "amount":1,
         "description":"tsp",
         "grams":5.38
      }
   ],
   "default_units":2,
   "nutrition":{ 
      "calories":598,
      "carbs":22.31,
      "fats":51.36,
      "proteins":22.21,
   },
   "serving_calories":95,
   "serving_carbs":3,
   "serving_fats":8,
   "serving_proteins":2,
}

What I want to do is go over each Doc in the collection and update the "serving_calories" field to be as follows:

serving_calories = doc.nutrition.calories * (doc.weights[doc.default_units].grams / 100);

So executing this properly would make serving_calories = 598 * (16 / 100) = ‭95.68‬

I wasn't able to find the right commands to be able to do the array index as needed from another value. What is the right mongo command to execute?

mongodb shell javascript
2 Answers

I figured I was not selecting the correct database before executing the script. Here is my script anyways:

db.foods.find().forEach(function(doc) {
  db.foods.updateOne({ _id: doc._id },{ $set: { 
    serving_calories: doc.nutrition.calories *  (doc.weights[doc.default_units].grams / 100)
  } } );
});
user_4138430

This can be done in an atomic update operation with MongoDB 4.2 and newer. The new versions have the update method which now takes a document or an aggregate pipeline where the following stages can be used:

Armed with the above, your update operation with the aggregate pipeline will have three steps i.e.

  1. Get the document in the weights array that corresponds to the index depicted by the default_units field and set it to a new field serving_weight to be used in the next pipeline.
  2. Set the serving_calories field with the result of the above computation.
  3. Remove the new field from stage 1 serving_weight

Your update operation follows:

db.foods.update(
    { },
    [
        { "$set": {
            "serving_weight": {
                "$arrayElemAt": ["$weights", "$default_units"]
            }
        } },
        { "$set": {
            "serving_calories": {
                "$multiply": [
                    "$nutrition.calories",
                    { "$divide": [ "$serving_weight.grams", 100 ] }
                ]
            }
        } },
        { "$unset": [ "serving_weight" ] }
    ]
);

You would of course need to add in logic for validation that is where elements don't exist, you would need to use $ifNull and substitute the appropriate values or default values.

user_1220050
Related