Update an object in array or insert into the array using a unique field-CodePudding

I have a document which holds an array property having object elements.

E.G.

{
    "_id": "61c01098bc29520008efc00f",
    "name": "codedump",
    "employments": [
         {
              "type": "PERMANENT",
              "salary": 1000000000
         },
         {
              "type": "TEMP",
              "salary": 1000000000
         },
         {
              "type": "PART_TIME",
              "salary": 1000000000
         },
    ]
}

And while updating the employment salary using type if the type is already in employments then i need to update the salary only. Else I need to push the new item to the array. I'm trying to find a way to make this work in a single query.

If the type is already present in array(for update):

/// Update data:
             {
                  "type": "PERMANENT",
                  "salary": 10
             }
// For this case I want the example document to become:
{
    "_id": "61c01098bc29520008efc00f",
    "name": "codedump",
    "employments": [
         {
              "type": "PERMANENT",
              "salary": 10 // From 1000000000 to 10         
         },
         {
              "type": "TEMP",
              "salary": 1000000000
         },
         {
              "type": "PART_TIME",
              "salary": 1000000000
         },
    ]
}

If the type is not present:

/// Update data:
             {
                  "type": "INVESTMENT",
                  "salary": 10000
             }
// For this case I want the example document to become:
{
    "_id": "61c01098bc29520008efc00f",
    "name": "codedump",
    "employments": [
         {
              "type": "PERMANENT",
              "salary": 1000000000      
         },
         {
              "type": "TEMP",
              "salary": 1000000000
         },
         {
              "type": "PART_TIME",
              "salary": 1000000000
         },
         {
              "type": "INVESTMENT",
              "salary": 10000
         }
    ]
}

CodePudding user response：

I don't know if what you want is posible in one single operation...

With that said, how I would do it is, using the $ positional operator to update the array element that I want. Doc & playground.

db.collection.update({
  "employments.type": "PERMANENT",
  
},
{
  "$set": {
    "employments.$.salary": 10
  }
},
{
  "multi": false,
  "upsert": false
})

This operation would only work if the element exist in the array. If it doesn't the operation would fail. Then you can catch the exception in your code and perform an insertion with $push. Playground

db.collection.update({},
{
  "$push": {
    "employments": [
      {
        "type": "INVESTMENT",
        "salary": 10000
      }
    ]
  }
},
{
  "multi": false,
  "upsert": false
})

CodePudding user response：

It's technically possible in 1 call, starting in Mongo v4.2 you can use pipelined updates, this essentially allows you to use aggregation operators in the update body, thus you can achieve pretty much anything.

I will say however it's definitely not very efficient, it really depends what part of your system you want to maximize/minimize (network / db load / etc).

This is how you can do it:

const input =  {
  "type": "INVESTMENT",
  "salary": 10000
}

db.collection.update({},
[
  {
    $set: {
      employments: {
        $cond: [
          {
            $in: [
              input.type,
              "$employments.type"
            ]
          },
          {
            $map: {
              input: "$employments",
              in: {
                $cond: [
                  {
                    $eq: [
                      "$$this.type",
                      input.type,
                    ]
                  },
                  {
                    type: "$$this.type",
                    salary: input.salary
                  },
                  "$$this"
                ]
              }
            }
          },
          {
            $concatArrays: [
              "$employments",
              [
                input
              ]
            ]
          }
        ]
      }
    }
  }
])

Mongo Playground