Update a MongoDB subdocument when the parent document might not exist

Question

Here's my data, consisting of a books collection, with a books.reviews sub-collection.

books = [{
    _id: ObjectId("5558f40ad498c653748cf045"),
    title: "Widget XYZ",
    isbn: 1234567890,
    reviews: [
        {
            _id: ObjectId("5558f40ad498c653748cf046"),
            userId: "01234",
            rating: 5,
            review: "Yay, this great!"
        },
        {
            _id: ObjectId("5558f40ad498c653748cf047"),
            userId: "56789",
            rating: 3,
            review: "Meh, this okay."
        }
    ]
}]

In Node.js (using Mongoose), I need for users to be able to submit reviews via a form, sending the review and the isbn of the book to the server, with the following conditions:

1. If the book doesn't exist already with a specific isbn, add it, then add the review (it obviously doesn't already exist).
2. If the book does exist...
   • If the review doesn't exist for this book for this user, add it.
   • If the review does exist for this book for this user, update it.

I can do this with 4 separate queries, but I know that's not optimal. What are the fewest number of queries I could use (and of course, what are those queries)?

Answer 1

You basically have 3 cases:

1. both the book and the review exists. This is a simple $set
2. the book exists but not the review. This need a $push
3. the book does not exists. This need {upsert:1} and a $setOnInsert

I was not able to find a way to unify any two of these without compromising data integrity in case of failure (remember that MongoDB does not have atomic transaction).

So my best idea is the following:

// Case 1:
db.books.update({isbn:'1234567890',
                 review: { $elemMatch: {userID: '01234'}}},
                {$set: {'review.$.rating': NEW_RATING}}
               )

// Case 2:
db.books.update({isbn:'1234567890',
                 review: { $not: { $elemMatch: {userID: '01234'}}}},
                {$push: {review: {rating: NEW_RATING, userID:'01234'}}}
               )

// Case 3:
db.books.update({isbn:'1234567890'},
                {$setOnInsert: {review: [{rating: NEW_RATING, userID:'01234'}]}},
                {upsert:1}
               )

You may blindly run those three updates in a raw as there is no overlapping case between them. The beauty of the thing is all these operations are idempotent. So you can apply them once or several times and always get the same result. This is especially important in case of failover. In addition, there is no way for your DB to be inconsistent or to loose existing data in case of failure. At worst, the review is not updated. Finally this should guarantee data consistency even in case of concurrent updates (i.e.: in that case, one update will overwrite the other, but you shouldn't end up having two documents for the same book or two reviews of the same user for the same book).
_{That later point has to be confirmed as it is late here so my analysis might be somewhat doubtful.}

As final note, if you want to reduce the number of round-trips between MongoDB and your app, you might take a look at the update database command allowing you to wrap several updates in one command.