Is there a way to upsert a list with a single query?

Question

I know this question has been asked before, but that's a different scenario. I'd like to have a collection like this:

{
    "_id" : ObjectId("4c28f62cbf8544c60506f11d"),
    "pk": 1,
    "forums": [{
        "pk": 1,
        "thread_count": 10, 
        "post_count": 20,
    }, {
        "pk": 2,
        "thread_count": 5, 
        "post_count": 24,
    }]
}

What I want to do is to upsert a "forum" item, incrementing counters or adding an item if it does not exist.

For example to do something like this (I hope it makes sense):

db.mycollection.update({
    "pk": 3,
    "forums.pk": 2
}, {
    "$inc": {"forums.$.thread_count": 1},
    "$inc": {"forums.$.post_count": 1},
}, true)

and have:

{
    "_id" : ObjectId("4c28f62cbf8544c60506f11d"),
    "pk": 1,
    "forums": [{
        "pk": 1,
        "thread_count": 10, 
        "post_count": 20,
    }, {
        "pk": 2,
        "thread_count": 5, 
        "post_count": 24,
    }]
},
{
    "_id" : ObjectId("4c28f62cbf8544c60506f11e"),
    "pk": 3,
    "forums": [{
        "pk": 2,
        "thread_count": 1, 
        "post_count": 1,
    }]
}

I can surely make it in three steps:

1. Upsert the whole collection with a new item
2. addToSet the forum item to the list
3. increment forum item counters with positional operator

That's to say:

db.mycollection.update({pk:3}, {pk:3}, true)
db.mycollection.update({pk:3}, {$addToSet: {forums: {pk:2}}})
db.mycollection.update({pk:3, 'forums.pk': 2}, {$inc: {'forums.$.thread_counter': 1, {'forums.$.post_counter': 1}})

Are you aware of a more efficient way to do it? TIA, Germano

Answer 1

As you may have discovered, the positional operator cannot be used in upserts:

The positional operator cannot be combined with an upsert since it requires a matching array element. If your update results in an insert then the "$" will literally be used as the field name.

So you won't be able to achieve the desired result in a single query.

You have to separate the creation of the document from the counter update. Your own solution is on the right track. It can be condensed into the following two queries:

// optionally create the document, including the array
db.mycollection.update({pk:3}, {$addToSet: {forums: {pk:2}}}, true)

// update the counters in the array item
db.mycollection.update({pk:3, 'forums.pk': 2}, {$inc: {'forums.$.thread_counter': 1, 'forums.$.post_counter': 1}})