5

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

Horace
  • 346
  • 2
  • 14
  • How are you checking to see if a review already exists? I can think of a way to do this in one query, but it assumes a new review. – Jordan P May 17 '15 at 22:45

1 Answers1

5

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.

Sylvain Leroux
  • 50,096
  • 7
  • 103
  • 125
  • I've spent 20 minutes trying to think of ways to break this solution, but it seems really solid. However, what would happen if I added `{upsert:1}` to the "Case 2"? – Horace May 18 '15 at 23:25
  • 1
    @Horace If you add `{upsert:1}` to case 2, it is not longer idempotent: issuing that same command twice will result in several inserts. This is acceptable, _if and only if_ you take extra cares in case of failover. Maybe simply by running step 2 only if step 1 didn't have a match (BTW, and running step 3 only if step 2 didn't have a match either); this is a simple `if ... else if .... else ...` logic -- but you are now more exposed to "human" error on code maintenance. OTOH this is less load on the DB. But you can no longer just send a bulk update containing all three updates in a raw... – Sylvain Leroux May 19 '15 at 05:07
  • 1
    This is working great. I'm executing your cases in a single call to the server via `BookModel.db.db.command({update:'books', ...})`, and it's very fast. I worry, though, about side stepping Mongoose's schema enforcement. – Horace May 20 '15 at 15:55