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

user_11091571 0
Viewed 34

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

mongoose mongodb
1 Answers

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

user_107703700
Related