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

Question

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
         }
    ]
}

Tom Slabbaert · Accepted Answer · 2021-12-29 10:13:40Z

2

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

answered Dec 29, 2021 at 10:13

Tom Slabbaert

22.5k10 gold badges34 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

codedump Over a year ago

Any specific reason for this query being inefficient? I want to minimize db load, multiple db calls and reduce code.

codedump Over a year ago

This snippet is giving me Error: MongoError: The dollar ($) prefixed field '$set' in '0.$set' is not valid for storage.

Tom Slabbaert Over a year ago

I recommend you first use the playground link, it shows it's valid. at that point you should figure out what changes you made to break it. Also make sure you're on Mongo v4.2+

codedump Over a year ago

Yes i found out the error. I was using this logic to perform updates on nested array. Seems index based selection doesn't work with update pipeline e.g. parent.0.user.employments. Works fine for not nested tho. Do you have any ideas for nested arrays?

Tom Slabbaert Over a year ago

If you want to ask a different question to perform a different type of update feel free to do so, but the pipeline i provided does what the question asked. imagine it's an aggregation pipeline so you don't have access to native update operators within it.

|

Rubén Vega · Accepted Answer · 2021-12-29 08:07:51Z

1

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
})

answered Dec 29, 2021 at 8:07

Rubén Vega

7307 silver badges14 bronze badges

2 Comments

codedump Over a year ago

Yes i had thought about that. But I was trying to explore a single query option. There was $addToSet but for updating or pushing objects inside array it is practically useless. Thanks for the answer!

Rubén Vega Over a year ago

As I said i don't know another way, the upsert doesn't work inside the arrays. But maybe someone knows a better one.

Collectives™ on Stack Overflow

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

2 Answers 2

6 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related