Conditionally update/upsert embedded array with findOneAndUpdate in MongoDB

Question

I have a collection in the following format:

[
  {
    "postId": ObjectId("62dffd0acb17483cf015375f"),
    "userId": ObjectId("62dff9584f5b702d61c81c3c"),
    "state": [
      {
        "id": ObjectId("62dffc49cb17483cf0153220"),
        "notes": "these are my custom notes!",
        "lvl": 3,
        
      },
      {
        "id": ObjectId("62dffc49cb17483cf0153221"),
        "notes": "hello again",
        "lvl": 0,
      },
    ]
  },
]

My goal is to be able to update and add an element in this array in the following situation:

1. If the ID of the new element is not in the state array, push the new element in the array
2. If the ID of the new element is in the state array and its lvl field is 0, update that element with the new information
3. 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 were matched.

Basically, to accomplish this I was thinking about using findOneAndUpdate with upsert, but I am not sure how to tell the query to update the state if lvl is 0 or don't do anything if it is bigger than 0 when the match is found.

For solving (1) this is what I was able to come up with:

db.collection.findOneAndUpdate(
    {
        "postId": ObjectId("62dffd0acb17483cf015375f"),
        "userId": ObjectId("62dff9584f5b702d61c81c3c"),
        "state.id": {
            "$ne": ObjectId("62dffc49cb17483cf0153222"),
        },
    },
    {
        "$push": {"state": {"id": ObjectId("62dffc49cb17483cf0153222"), "lvl": 1}}
    },
    {
        "new": true,
        "upsert": true,
    }
)

What is the correct way to approach this issue? Should I just split the query into multiple ones?

Edit: as of now I have done this in more than one query (one to fetch the document, then I iterate over its state array to check if the ID exists in it, and then I perform (1), (2) and (3) in a normal if-else clause)

Answer 1

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.