How to increase a field of an element in a subarray, or insert the element into the subarray if missing

Working with Data
,
1

I have a collection called files_collection with the following structure:

{
   "filename":"file1",
   "users":[
      {
         "username":"user1",
         "score":50
      }{
         "username":"user2",
         "score":70
      }
   ]
},
{
   "filename":"file2",
   "users":[
      {
         "username":"user1",
         "score":12
      }{
         "username":"user3",
         "score":15
      }
   ]
}

The filename is unique and always exists already. My goal is to create a single query that increases the score of a user that might not exist already for a given file. This leads to two cases:

  1. If the username already exists for the given file, increases the user’s score by a given number.
  2. If the username for the file doesn’t exist yet, inserts a new element with the given username and score.

So for instance with the previous structure, given username “user3” and score 12 for the first file would imply inserting a new element, while if the name was “user1” it would just increase that user’s score.

My current solution (using PyMongo) is the following:

files_collection.update_one({"filename": filename, "users.name" : {"$elemMatch" : {"username" : user}}}, 
                                 {"$inc":{"users.$ .score":score}},
                                 upsert=True)

But this generates a DuplicateKeyError for the filename. I assume it tries to not just insert an element into a subarray but instead everything including the filename. What would be the best way just insert into the subarray, in a single query, so that it is performed atomically?