Update a mongo field based on another collections data

Question

I'm looking for a way to update a field based on the sum of the data of another collection.

I tried to bring all the meals and use forEach to call the Products collection for each meal, tested if it was working, but I got a time out.

meals.find().forEach(meal => {
    var products = db.Products.find(
        { sku: { $in: meal.products } },
        { _id: 1, name: 1, sku: 1, nutritional_facts: 1 }
    )
    printjson(products)
})

My goal was to execute something like this below to get the desired result, but I got "SyntaxError: invalid for/in left-hand side". Is not possible to use for in inside a mongo query?

db.Meals.find({}).forEach(meal => {

    const nutri_facts = {};

    db.Products.find({ sku: { $in: meal.products } }, 
    { _id: 1, name: 1, sku: 1, nutri_facts: 1 }).forEach(product => {
        for (let nutriFact in product.nutri_facts) {
            nutri_facts[nutriFact] =
                    parseFloat(nutri_facts[nutriFact]) +
                    parseFloat(product.nutri_facts[nutriFact]);
            }
        }
    });

    for (let nutriFact in nutri_facts) {
            meal.nutri_facts[nutriFact] = 
            nutri_facts[nutriFact];
        }
    }

    db.Meals.updateOne({ _id: meal._id }, meal)
});

I also had a hard time trying to figure out how to use aggregate and lookup in this case but was not successful.

Is it possible to do that?

Example - Meals Document

{
  _id: ObjectId("..."),
   products : ["P068","L021","L026"], //these SKUs are part of this meal
   nutri_facts: {
    total_fat: 5g,
    calories: 100kcal
    (...other properties)
   }
}

For each meal I need to look for its products on 'Products' collections using 'sku' field. Then I will sum the nutritional facts of all products to get the meal nutritional facts.

Example Products Document

{
  _id: ObjectId("..."),
   sku: 'A010'
   nutri_facts: {
    total_fat: 2g,
    calories: 40kcal
    (...other properties)
   }
}

I know that mongo might not be the best option in this case, but the entire application is already built using it.

Answer 1

For each meal I need to look for its products on 'Products' collections using 'sku' field. Then I will sum the nutritional facts of all products to get the meal nutritional facts.

db.Meals.find( { } ).forEach( meal => {

    // print(meal._id);
    const nutri_facts_var = { };

    db.Products.find( { sku: { $in: meal.products } }, { nutri_facts: 1 }.forEach( product => {

         // printjson(product.nutri_facts);

        for ( let nutriFact in product.nutri_facts ) {

            let units = (product.nutri_facts[nutriFact].split(/\d+/)).pop();
            // print(units)

            // Checks for the existence of the field and then adds or assigns
            if ( nutri_facts_var[nutriFact] ) {
                nutri_facts_var[nutriFact] = parseFloat( nutri_facts_var[nutriFact] ) + parseFloat( product.nutri_facts[nutriFact] );
            }
            else {
                nutri_facts_var[nutriFact] = parseFloat( product.nutri_facts[nutriFact] );
            }

            nutri_facts_var[nutriFact] = nutri_facts_var[nutriFact] + units;
        }

    } );

   // printjson(nutri_facts_var);

   db.Meals.updateOne( { _id: meal._id }, { $set: { nutri_facts: nutri_facts_var } } );
} );

NOTES:

1. I use the variable nutri_facts_var name ( the var suffixed) so that we can distinguish the user defined variable names easily from the document fields names.
2. { _id: 1, name: 1, sku: 1, nutri_facts: 1 } changed to { nutri_facts: 1 }. The _id is included by default in a projection. The fields name and sku are not needed.
3. db.Meals.updateOne({ _id: meal._id }, meal) is not a correct syntax. The update operations use Update Operators. The corrected code: db.Meals.updateOne( { _id: meal._id }, { $set: { nutri_facts: nutri_facts_v } } ). Note we are updating the nutifacts only, not all details.
4. Since the individual nutrifacts are stored as strings (e.g., "100kcal"), during arithmetic the string parts are stripped. So, we capture the string units (e.g., "kcal") for each nutrifact and append it later after the arithmetic. The following code strips and stores the units part: let units = (product.nutri_facts[nutriFact].split(/\d+/)).pop().
5. Use the mongo shell methods print and printjson to print the contents of a variable or an object respectively - for debugging purposes.

Note the query updates the Meal collection even if the nutri_facts field is not defined in it; the $set update operator creates new fields and sets the values in case the fields do not exist.