Update Array where it exists or Insert new Array Item

Question

{
 _id:'1',
name:'apple',
option:[{
weight:'10',
price:'40',
},
{weight:'40',
price:'200'}]
}

I want to add apple if its not in db, if its in db, I want to ckeck if weight 10 is available, if available I want to update the price if not I want to add new weight and price in option. How can I do it, in mongodb.

Answer 1

You want .bulkWrite() for this. This is not actually a single operation, so you want to submit multiple operations in a single request. Essentially attempt to write the update with $set where data does exist or $push the new data where it does not exist:

db.collection.bulkWrite([
  { "updateOne": {
    "filter": { "_id": "1", "option.weight": "10" },
    "update": { 
      "$set": { "option.$.price": "30" }
    }
  }},
  { "updateOne": {
    "filter": { "_id": "1", "option.weight": { "$ne": "10" } },
    "update": {
      "$push": { "option": { "weight": "10", "price": "30" } }
    }
  }}
])

The positive case is simply the value, and the $ne "negates" the equality match, meaning the item does not exist. Of course the positional $ operator is used with $set where it does

Given the data only one of the operations will actually match and apply as an update despite two operations being sent in the "batch".

If you want "upserts" for the whole document as well, then you need to add another operation to the end of that. Note that you cannot apply "upsert" as an option on either of the other statements, especially the $ne because that would create a new document where the array item does not exist, not just the _id:

db.collection.bulkWrite([
  { "updateOne": {
    "filter": { "_id": "1", "option.weight": "10" },
    "update": { 
      "$set": { "option.$.price": "30" }
    }
  }},
  { "updateOne": {
    "filter": { "_id": "1", "option.weight": { "$ne": "10" } },
    "update": {
      "$push": { "option": { "weight": "10", "price": "30" } }
    }
  }},
  { "updateOne": {
    "filter": { "_id": 1 },
    "update": {
      "$setOnInsert": {
        "option": [
           { "weight": "10", "price": "30" }
         ]
      }
    },
    "upsert": true
  }}
])

The $setOnInsert is the main help here apart from that last operation being the only marked as "upsert". That combination makes sure that where the primary "document" is found then nothing actually happens, but when it's not found the new array item is added.

As a side note, I would strongly suggest storing numeric values actually as numeric rather than strings. Not only does it save on space in most cases but it's also far more useful that way.