Conditionally update/upsert embedded array with findOneAndUpdate in MongoDB

If the ID of the new element exists in the array, and its lvl field is not 0, then nothing should happen. I will throw an error by seeing that no documents where matched.

First thing FYI,

• upsert is not possible in the nested array
• upsert will not add new elements to the array
• upsert can add a new document with the new element
• if you want to throw an error if the record does not present then you don't need upsert

Second thing, you can achieve this in one query by using an update with aggregation pipeline in MongoDB 4.2,

Note: Here i must inform you, this query will respond updated document but there will be no flag or any clue if this query fulfilled your first situation or second situation, or the third situation out of 3, you have to check in your client-side code through query response.

• check conditions for postId and userId fields only
• we are going to update state field under $set stage
• check the condition if the provided id is present in state's id?
  • true, $map to iterate loop of state array
    • check conditions for id and lvl: 0?
      • true, $mergeObjects to merge current object with the new information
      • false, it will not do anything
  • false, then add that new element in state array, by $concatArrays operator

db.collection.findOneAndUpdate(
  {
    postId: ObjectId("62dffd0acb17483cf015375f"),
    userId: ObjectId("62dff9584f5b702d61c81c3c")
  },
  [{
    $set: {
      state: {
        $cond: [
          { $in: [ObjectId("62dffc49cb17483cf0153221"), "$state.id"] },
          {
            $map: {
              input: "$state",
              in: {
                $cond: [
                  {
                    $and: [
                      { $eq: ["$$this.id", ObjectId("62dffc49cb17483cf0153221")] },
                      { $eq: ["$$this.lvl", 0] }
                    ]
                  },
                  {
                    $mergeObjects: [
                      "$$this",
                      { 
                        // update your new fields here
                        "notes": "new note" 
                      }
                    ]
                  },
                  "$$this"
                ]
              }
            }
          },
          {
            $concatArrays: [
              "$state",
              [
                // add new element
                {
                  "id": ObjectId("62dffc49cb17483cf0153221"),
                  "lvl": 1
                }
              ]
            ]
          }
        ]
      }
    }
  }],
  { returnNewDocument: true }
)

Playrgound

Third thing, you can execute 2 update queries,

• The first query, for the case: element does not present and it will push a new element in state

let response = db.collection.findOneAndUpdate({
  postId: ObjectId("62dffd0acb17483cf015375f"),
  userId: ObjectId("62dff9584f5b702d61c81c3c"),
  "state.id": { $ne: ObjectId("62dffc49cb17483cf0153221") }
},
{
  $push: {
    state: {
      id: ObjectId("62dffc49cb17483cf0153221"),
      lvl: 1
    }
  }
},
{
  returnNewDocument: true
})

The second query on the base of if the response of the above query is null then this query will execute,

• This will check state id and lvl: 0 conditions if conditions are fulfilled then execute the update fields operation, it will return null if the document is not found
• You can throw if this will return null otherwise do stuff with response data and response success

if (response == null) {
  response = db.collection.findOneAndUpdate({
    postId: ObjectId("62dffd0acb17483cf015375f"),
    userId: ObjectId("62dff9584f5b702d61c81c3c"),
    state: { 
      $elemMatch: {
        id: ObjectId("62dffc49cb17483cf0153221"),
        lvl: 0
      }
    }
  },
  {
    $set: {
      // add your update fields
      "state.$.notes": "new note"
    }
  },
  {
    returnNewDocument: true
  });
  
  // not found and throw an error
  if (response == null) {
    return {
      // throw error;
    };
  }
}

// do stuff with "response" data and return result

return {
  // success;
};

Note: As per the above options, I would recommend you that I explained in the Third thing that you can execute 2 update queries.

What you're trying became possible with the introduction pipelined updates, here is how I would do it by using $concatArrays to concat the exists state array with the new input and $ifNull in case of an upsert to init the empty value, like so:

const inputObj = {
                  "id": ObjectId("62dffc49cb17483cf0153222"),
                  "lvl": 1
                };

db.collection.findOneAndUpdate({
  "postId": ObjectId("62dffd0acb17483cf015375f"),
  "userId": ObjectId("62dff9584f5b702d61c81c3c")
},
[
  {
    $set: {
      state: {
        $ifNull: [
          "$state",
          []
        ]
      },
      
    }
  },
  {
    $set: {
      state: {
        $concatArrays: [
          {
            $map: {
              input: "$state",
              in: {
                $mergeObjects: [
                  {
                    $cond: [
                      {
                        $and: [
                          {
                            $in: [
                              inputObj.id,
                              "$state.id"
                            ]
                          },
                          {
                            $eq: [
                              inputObj.lvl,
                              0
                            ]
                          }
                        ]
                      },
                      inputObj,
                      {},
                      
                    ]
                  },
                  "$$this"
                ]
              }
            }
          },
          {
            $cond: [
              {
                $not: {
                  $in: [
                    inputObj.id,
                    "$state.id"
                  ]
                }
              },
              [
                
              ],
              []
            ]
          }
        ]
      }
    }
  }
],
{
  "new": true,
  "upsert": true
})

Mongo Playground

Prior to version 4.2 and the introduction of this feature what you're trying to do was not possible using the naive update syntax, If you are using an older version then you'd have to split this into 2 separate calls, first a findOne to see if the document exists, and only then an update based on that. obviously this can cause stability issue's if you have high update volume.