How do I implement this mongodb query & update ope

Question

I have this collection:

Books
[
{
  title: Book1,
  References: [ObjectId(1), ObjectId(3), ObjectId(5)] <- These are object ids of another collection
  Main-Reference: ObjectId(5)
},
{
  title: Book2,
  References: [ObjectId(2), ObjectId(5), ObjectId(7)] 
  Main-Reference: ObjectId(5)
}
{
  title: Book3,
  References: [ObjectId(5), ObjectId(7), ObjectId(9)] 
  Main-Reference: ObjectId(7)
},
]

I have an operation where I delete a Reference from book collection Example: Assume I have to delete Reference ObjectId(5) from my collection So my new collection become this:

Books
[
{
  title: Book1,
  References: [ObjectId(1), ObjectId(3)] <- ObjectId(5) is pulled
  Main-Reference: ObjectId(1) <- ObjectId(1) is new value as ObjectId(5) is deleted
},
{
  title: Book2,
  References: [ObjectId(2), ObjectId(7)]  <- ObjectId(5) is pulled
  Main-Reference: ObjectId(2) <- ObjectId(2) is now main reference
}
{
  title: Book3,
  References: [ObjectId(7), ObjectId(9)] <- ObjectId(5) is pulled
  Main-Reference: ObjectId(7) <- no changes here as ObjectId(7) still exists in References
},
]

Currently this is how I am doing:

Step 1: Pull ObjectId(5) from all Books where References[] has ObjectId(5)

Step 2: Query Books collection where Main-Reference=ObjectId(5) & use References: {$slice:1} slice to get the first array element from References array

Step 3: Update all of the books found in Step 2 & replace Main-Reference with the first array element I get from slice

This seems clumsy to me and trying to see if there is a better way to do this.

Answer 1

If I essentially get your gist you basically want to

1. Pull the item that is not required from your references array
2. Set the value of your main-reference field to the first element of the altered array

And get that done all in one update without moving documents across the wire.

But this sadly cannot be done. The main problem with this is that there is no way to refer to the value of another field within the document being updated. Even so, to do this without iterating you would also need to access the changed array in order to get the new first element.

Perhaps one approach is to re-think your schema in order to accomplish what you want. My option here would expanding on your references documents a little and removing the need for the main-reference field.

It seems that the assumption you are willing to live with on the updates is that if the removed reference was the main-reference then you can just set the new main-reference to the first element in the array. With that in mind consider the following structure:

refs: [ { oid: "object1" }, { oid: "object2" }, { oid: "object5", main: true } ]

By changing these to documents with an oid property that would be set to the ObjectId it gives the option to have an additional property on the document that specifies which is the default. This can easily be queried determine which Id is the main reference.

Now also consider what would happen if the document matching "object5" in the oid field was pulled from the array:

refs: [ { oid: "object1" }, { oid: "object2" } ]

So when you query for which is the main-reference as per the earlier logic you accept the first document in the array. Now of course, to your application requirements, if you want to set a different main-reference you just alter the document

refs: [ { oid: "object1" }, { oid: "object2", main: true } ]

And now the logic remains to choose the array element that has the main property as true would occur in preference, and as shown above that if that property does not exist on any elements document then fall back to the first element.

With all of that digested, your operation to pull all references to an object out of that array in all documents becomes quite simple, as done in the shell ( same format should basically apply to whatever driver ):

db.books.update(
   { "refs.oid": "object5" },
   { $pull: { refs: {oid: "object5"} } }, false, true )

The two extra arguments to the query and update operation being upsert and multi respectively. In this case, upsert does not make much sense as we only want to modify documents that exist, and multi means that we want to update everything that matched. The default is to change just the first document.

Naturally I shortened all the notation but of course the values can be actual ObjectId's as per your intent. It seemed also reasonable to presume that your main usage of the main-reference is once you have retrieved the document. Defining a query that returns the main-reference by following the logic that was outlined should be possible, but as it stands I have typed a lot out here and need to break for dinner :)

I think this presents a worthwhile case for re-thinking your schema to avoid over the wire iterations for what you want to achieve.