CODEWITHSUNDEEP
mongodbpymongo
Andrew
Andrew

Reputation: 7883

How would I update a field in MongoDB which totals up the values of a child document?

I have a document which is structured like this:

{
    'item_id': '12345'
    'total_score': 100,
    'user_scores': {
        'ABC': 40,
        'DEF': 60
    }
}

I'm using PyMongo, but documentation of MongoDB seems easily translatable across different distributions. With PyMongo, I could update user scores with:

collection.update_one(
    { 'item_id': '12345' },
    { '$set': { 'user_scores.GHI': 20 } },
    upsert=True
)

Which results in this:

{
    'item_id': '12345'
    'total_score': 100,
    'user_scores': {
        'ABC': 40,
        'DEF': 60,
        'GHI': 20
    }
}

The issue is of course that the total_score is now incorrect. I want that total score to update, so that in a future query, I can quickly ascertain the score of each result, and even sort by score.

One solution could be to find an existing document using find_one({'item_id: '12345'}), (create if it doesn't exist), then update with new scores, and update total score. The problem there is that I want to run thousands of these at the same time, and it's far more efficient to call bulk_write on a series of requests.

So, a better solution would be to do two sequential update requests:

request1 = UpdateOne(
    { 'item_id' : '12345' },
        { '$set': { 'user_scores.GHI': 20 } },
        upsert = True
)

request2 = UpdateOne(
    { 'item_id' : '12345' },
        { '$set': { 'total_score': { '$sum': { '$values': 'user_scores' } } } },
        upsert = True
)

The first request updates the user scores, same as before. The second request, there are two concepts going on. The syntax for this isn't correct, but here's what I'm trying to do:

  1. I need to get the values from the user_scores dictionary. { '$values': 'user_scores' } is how I've tried to convey this.
  2. That gives me an array of values. I know these are all numeric, so I now need to sum those, conveyed with { '$sum': { '$values': 'user_scores' } }.

I can run these batch updates consecutively, so there's no risk of summing the wrong thing. The danger with having a total_score field will always be that it isn't updated and thus doesn't contain the correct number. I'd imagine this is a common case with document-based models?

Upvotes: 1

Views: 314

Answers (1)

Tom Slabbaert
Tom Slabbaert

Reputation: 22316

If you're using Mongo version 4.2+ they introduced a new feature: pipelined updates, Meaning now you can do what you want in one go:

db.collection.updateOne({ 'item_id' : '12345' },
    [
        { '$set': { 'user_scores.GHI': 20 } },
        { '$set': { 'total_score': { '$sum': [ "$user_scores.GHI", "$user_scores.ABC", "$user_scores.GHI"] } } },,
    ]);

Unfortunately this is not possible for lesser Mongo versions hence if that is the case you'll have to keep using your solution which is splitting this into 2 actions.

EDIT: For dynamic update we can use $map and $objectToArray like so:

db.collection.updateOne(
    {'item_id': '12345'},
    [
        {'$set': {'user_scores.GHI': 20}},
        {
            '$set':
                {
                    'total_score': {
                        '$sum': {
                            '$map': {
                                'input': {'$objectToArray': '$user_scores'},
                                'as': 'score',
                                'in': '$$score.v'
                            }
                        }
                    }
                }
        }
    ]);

Upvotes: 2

Related Questions