Conditionally update/upsert embedded array with findOneAndUpdate in MongoDB-CodePudding

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:

If the ID of the new element is not in the state array, push the new element in the array
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
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)

CodePudding user response：

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.

CodePudding user response：

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.