Mongoose - upsert if key doesnt exists and update if key exists-CodePudding

bit of a strange query.

I have the below. collection accounts and i have script which will run and needs to do the following:

if fileNumber exists AND balance array([{ value, date}]) already has the item[11] value dont update
if fileNumber exists and balance array DOESNT have the value - update the balance array
if fileNumber doesnt exists upsert one

Account.findOneAndUpdate(
    { fileNumber: item[0], 'balance.value': { $ne: item[11] } },
    {
        $set: {

            clientCode: item[1],
            clientRef: item[2],
            name: item[3],
            phoneNumber1: item[4],
            phoneNumber2: item[5],
            phoneNumber3: item[6],
            address1: item[7],
            postcode: item[8],
            exec: item[9],
            dateOfBirth: item[10],
            instalmentAmount: item[12],
        },
        $addToSet: { balance: { value: item[11], date: Date.now() } },
    },
    { upsert: true },
);

but at the moment it only wors half way. It will upsert if fileNumber exists but value in balance array doesnt, which i dont want

Could anyone help please?

Thanks

CodePudding user response：

You can use the following update:

The way it works:

We execute $replaceRoot in the update body, in it there is a condition:

if item[11] is in balance.value then we just use the existing "$$ROOT" in the update - i.e not changing anything.
if item[11] does not exist then we "merge" two objects, the first is the input object you created, the second is the ROOT object. so if the "root" exists no values will get updated as they will overwrite the input fields, if the root does not exist the new input fields will get populated. Lastly we just need to push a the new object to the balance array, regardless of which of the two cases it is.

db.collection.update({
  fileNumber: item[0]
},
[
  {
    $replaceRoot: {
      newRoot: {
        $cond: [
          {
            $in: [
              item[11],
              {
                $ifNull: [
                  "$balance.value",
                  []
                ]
              }
            ]
          },
          "$$ROOT",
          {
            $mergeObjects: [
              {
                clientCode: item[1],
                clientRef: item[2],
                name: item[3],
                phoneNumber1: item[4],
                phoneNumber2: item[5]
                phoneNumber3: item[6],
                address1: item[7],
                postcode: item[8],
                exec: item[9],
                dateOfBirth: item[10],
                instalmentAmount: item[12]
              },
              "$$ROOT",
              {
                balance: {
                  $concatArrays: [
                    {
                      $ifNull: [
                        "$balance",
                        []
                      ]
                    },
                    [
                      {
                        value: item[11],
                        date: new Date()
                      }
                    ]
                  ]
                }
              }
            ]
          }
        ]
      }
    }
  }
],
{
  upsert: true
})

Mongo Playground