MongoDB upsert $in

Question

I have a bunch of records I want to upsert for specific product ids. Depending on previous calculations I want to record what type that product was in the current week/year.

Problem is that I can't figure out a way to do this except for one at a time. Right now I'm doing:

a_group.forEach(p => {
    db.abc.update({
        product_id: p._id,
        year: 2021
    }, { 
        $set: { 
            'abc.34': 'a'
        }
    }, {
        upsert: true
    });
});

Where a_group is just an array of products.

This is really heavy in case of a large products array. It just does a_group.length upsert operations.

Ideally I would like to do something like:

db.abc.update({
    product_id: { $in: a_group.map(p => p._id) },
    year: 2021
}, {
    $set: {
        'abc.34': 'a'
    }
}, {
    upsert: true,
    multi: true
});

Which would see that a_group is an array and try to match and upsert for every single item in the array. Except that doesn't work.

Any help would be very much appreciated.

Answer 1

The problem here is you want a separate upsert for each discreet value of the _id.

From the docs:

An upsert:

• Updates documents that match your query filter
• Inserts a document if there are no matches to your query filter

In the case of an upsert such as:

updateMany(
    {a:{$in[1,2,3]}},
    {$set:{b:true}},   
    {upsert: true}
)

If there exists a document containing a: 3, then it will match the query filter, and therefore that one document will be updated, and no inserts will occur.

In the event that no document matches any of the values passed to $in, a single new document will be upserted. Since the query has no way to determine which value of a you wanted, it will create a document containing {b:true}, but will leave a undefined.

What you probably want is a bulk operation that can perform many upsert operations with a single call to the database.

Using the mongosh shell, that might look like:

let ops = [];
a_group.forEach(p => {
   ops.push(
    { updateOne:
       {
         filter: {
                  product_id: p._id,
                  year: 2021
         },
         update: {$set: {'abc.34': 'a'}},
         upsert: true
    }
   );
});
db.abc.bulkWrite(ops)

Check the docs for the driver you are using to see how to do a bulk operation.