UpdateMany, Upsert does not perform insert

I am curious if there is some syntax that I am missing here:

db.staging_collection.updateMany(
   { "account_number":"288 - 1"},
   { $set: { "claim_diagnosis.$[element].diagnosis_code" : '200' ,"payers.$[pyr].payer_name" : 'Self Pay'} },
   { arrayFilters: [ { "element.diagnosis_code": {"$eq":'2'} },{ "pyr.payer_name": {"$eq":'name'} } ] ,upsert: true}
)

We’ve noticed that this will indeed update, but it will not insert. Do we need a $setOnInsert or some other condition? Any input would be great!

1 Like

I am not sure yet which operator you should use, but according to the docs:

If an upsert operation results in an insert, the query must include an exact equality match on the array field in order to use $[<identifier>] in the update statement.

But your query does not even include an array field. So it won’t work for inserts. I’ll take a look at $setOnInsert.

IMHO you’ll need 2 different queries. Because you can’t run them together, in this case, because $setOnInsert complains about the path.

This is the logic, that may well be wrong (not an expert or mongodb employee).

$set using no “exact match” query array and $setOnInsert are complementary, they do not overlap in terms of writes.

So,

  1. Using the update operator $set, hence it will update found documents
  2. Using the $setOnInsert. If there aren’t any matches for your query just insert, but you need to insert the full document, no dot notation allowed.

Example with $set
Example of $setOnInsert Error
Eample on $setOnInsert