Is it possible to utilize $addToSet multiple times in the same update?

Question

So here is what I want the documents in my collection to look like structurally:

{
    "_id": "123",
    "systems": [
        {
            "_id": "1338",
            "metrics": [
                "TEST"
            ]
        }
    ]
}

My goal is to be able to do a single update/insert (using upsert=True) for any instance that the system and/or metric does/doesn't exist in their respective arrays. Currently my only work around is making to update calls as follows:

if not collection.find_one({"_id": "123", "systems._id": "1338"}):
    collection.update(
        {"_id": "123"}, 
        {"$addToSet": {"systems": {"_id": "1338"}}}, 
        upsert=True)
collection.update(
    {"_id": "123", "systems._id": "1338"}, 
    {"$addToSet": {"systems.$.metrics": "TEST"}}, 
    upsert=True)

Thanks

score 3 · Accepted Answer · edited May 23 '17 at 12:23

You cannot apply the positional operator without a corresponding query field containing an array, but you need the positional operator to know the index in the systems array to $addToSet {"metrics": ["TEST"]}. You cannot also query based on {"systems._id": "1338"}, because this field might not exist yet in the document {"_id": "123"}. Thus, it's not possible to do it in a single request if we cannot combine multiple operations touching the same fields, but we cannot have conflicting mods in update.

A last hope would be if we had something similar to the $where operator in the update document syntax to allow us to execute arbitrary JavaScript to update the document. I suspect that we don't (and won't) have this.

The main problem is the array index on systems field. You can redesign your schema to get rid of this index, using the _id attribute as the new index:

{
    "_id": "123",
    "systems": {
        "1338": {
            "metrics": [
                "TEST"
            ]
        }
    }
}

With this change, you could do all in one operation:

db.test.update({_id: "123"}, {$addToSet: {"systems.1338.metrics": "TEST"}}, {upsert: true})

Another option is to use the following design:

{
    "_id": "123",
    "systems": {
        "metrics": {
            "1338": [
                "TEST"
            ]
        }
    }
}

The best design depends on what you intend to do.

EDIT:

You also have the option to update the document locally in your application and then send it back to the DB, but this option may not offer the kind of atomicity that I think you aim.

And about your current work around: You don't need the if. You can run both queries in order. But it's insecure if you have more than one client acessing your database.

Is it possible to utilize $addToSet multiple times in the same update?

1 Answers1

EDIT:

Linked