Atomically move object by ID from one array to another in same document

[Adrian]

I have data that looks like this:

{
  "_id": ObjectId("4d525ab2924f0000000022ad"),
  "arrayField": [
    { id: 1, other: 23 },
    { id: 2, other: 21 },
    { id: 0, other: 235 },
    { id: 3, other: 765 }
  ],
  "someOtherArrayField": []
}

Given a nested object's ID (0), I'd like to $pull the element from one array (arrayField) and $push it to another array (someOtherArrayField) within the same document. The result should look like this:

{
  "_id": ObjectId("id"), 
  "arrayField": [
    { id: 1, other: 23 },
    { id: 2, other: 21 },
    { id: 3, other: 765 }
  ],
  "someOtherArrayField": [
    { id: 0, other: 235 }
  ]
}

I realize that I can accomplish this with a find followed by an update, i.e.

db.foo.findOne({"_id": param._id})
.then((doc)=>{
  db.foo.update(
    {
      "_id": param._id
    },
    {
      "$pull": {"arrayField": {id: 0}},
      "$push": {"someOtherArrayField": {doc.array[2]} }
    }
  )
})

But I'm looking for an atomic operation like, in pseudocode, this:

db.foo.update({"_id": param._id}, {"$move": [{"arrayField": {id: 0}}, {"someOtherArrayField": 1}]}

Is there an atomic way to do this, perhaps using MongoDB 4.2's ability to specify a pipeline to an update command? How would that look?

[Robert Cochran]

Hi, 

In researching this, I noticed a Stack Overflow post here asked in 2018, and I assume you are the author of that post. The person who gave you the answer to that question, Pete Garafano, is quite correct with his solution. I copied his code and tested it on an example document and his solution is overall the best one.

When you break this problem down, you are asking the server to do two different array update operations on the same document: a $pull and a $addToSet. Those cannot be atomic and they have to be done using code similar to Garafano's.

You cannot do such an update using an aggregation pipeline atomically, either. An aggregation pipeline, by definition, contains at least one stage. You will have to have multiple $addFields stages in order to copy one element from arrayField to a new array field, then concatenate that new field into someOtherArrayField. Then there is the issue of eliminating that copied field from arrayField with the equivalent of a $pull. Finally, you are going to have to write the modified document to a new collection using $out (or replacing the source collection wholesale, which in my opinion is dangerous if no backup of the source collection exists and the aggregation code it is not well tested.) Using $out will in any case give you a processing bottleneck if large numbers of documents need to be written to a collection. I came up with this aggregation code to update someOtherArrayField, but as you can see, it doesn't get rid of the element in the source arrayField; it is not well tested and sure isn't atomic. I provide it only to show that there are multiple aggregation stages involved, and the considerable overhead of writing the result to an output collection. 

db.c1.aggregate( [

{ "$match" : { "_id" : ObjectId("5d3a5c96552a88145344022a") } },

{ "$addFields" : { "newArr" :  [ { "$arrayElemAt" : [ "$arrayField", 0 ] } ] } }, 

{ "$addFields" : { "someOtherArrayField" : { "$concatArrays" : [ "$someOtherArrayField", "$newArr" ] } } },

{ "$project" : { "_id" : 0, "newArr" : 0 } },

{ "$out" : "c2" }    

]

)

I think the above comments apply to version 4.2 as well. With the upcoming version, it is simply saying that, with some constraints, the update operator can accept an aggregation pipeline. You still must work with multiple aggregation stages, and while these allow for possibly more fine-grained updates, they will not be atomic. The very newness of the feature means it likely has some bugs in it too.

I think Pete Garafano gave best overall answer in the Stack Overflow post referenced. 

Please note that I am not an employee of MongoDB, Inc. I'm just another list user trying to help you.

Thanks so much

Bob

[Robert Cochran]

Hi,

I need to qualify what I said above after doing more research. I incorrectly give the impression that single-document write operations are not atomic. According to this article, single-document write operations are atomic. So to me that sounds like updating a single document, using Garafano's method, is indeed atomic if a $pull is considered a write operation and a $addToSet or $push is considered a write operation. Where it gets difficult is if you want to do write operations to multiple documents. For those, you can use transactions if you have a replicaset. 

An aggregation query by its nature will involve multiple documents. I'm not sure whether an aggregation query can be treated as a transaction and therefore the documents it processes could be part of a multi-document transaction. 

Thanks so much

Bob

[Adrian]

Bob, thanks for your interest in my question.  I did post to Stack Overflow, but it's not the one you linked to.  As you self-corrected, the push/pull pair are atomic.  Sorry for the confusion, but that's not what I was asking.  I wanted the find/update pair to be atomic.  See the answer to my Stack Overflow question for how it can work in 4.2.  I don't need multiple aggregation stages, and I definitely won't use $out.

[Asya Kamsky]

This question was reposted as a duplicate causing some confusion but Charlie Swanson correct gave an updated for 4.2 answer (since with aggregation syntax we can do everything in a single update now without having to query for the document first):

https://stackoverflow.com/a/57224243/431012

The pipeline allows complex transformation to the document which means you can reference other fields in the document during update.

Asya