MongoDB $inc and $setOnInsert for nested array

Question

I have searched SO and crawled the Mongoose / Mongo documentation but to no avail, therefore my question.

I would like to $inc a value in an object that lies within a nested array OR create $setOnInsert this object if it's not there yet.

The Mongo document I have looks as follows:

{
  "_id": "123",
  "members": [
    {
      "first": "johnny",
      "last": "knoxville",
      "score": 2
    },
    {
      "first": "johnny",
      "last": "cash",
      "score": 3
    },
    // ...and so on
  ],
}

Based on this example my use case is to:

• Increment the count variable inside the array object if it exists (found based on first and last)
• Add an object to the set with score as 1 if it does not exist yet

From this post I understood that I cannot $set a variable that I would like to $inc at the same time. Ok - that makes sense.

This post helped to understand the positional $ operator in order to find the nested object and increment it.

If I know that the document exists, I can simply do the update as follows:

myDoc = { first: 'johnny', last: 'cash' };

myCollection.findOneAndUpdate(
  {
    _id: '123',
    'members.first': 'johnny',
    'members.last': 'cash'
  },
  {
    $inc: { "members.$.score": 1 }
  }
);

But what if I would like to insert the member (with score: 1) if it doesn't exist yet?

My problem is that when I use upsert: true the positional operator throws an error since it may not be used with upsert (see the official documentation).

I have tried various combinations and would like to avoid 2 db accesses (read / write).

Is there a way to do this in ONE operation?

Answer 1

With MongoDB 4.2 and newer, the update method can now take a document or an aggregate pipeline where the following stages can be used:

1. $addFields and its alias $set
2. $project and its alias $unset
3. $replaceRoot and its alias $replaceWith.

Armed with the above, your update operation with the aggregate pipeline will be to override the members field based on a condition i.e. the template follows:

var myDoc = { first: 'johnny', last: 'cash', score: 1 };

db.getCollection('myCollection').update(
    { "_id" : "123" },
    [
        "$set": {
            "members": {
                "$cond": {
                    "if": {}, // does the members array contain myDoc?
                    "then": {}, // map the members array and increment the score
                    "else": {} // add myDoc to the existing members array
                }
            }
        }
    ]
);

To get the expression for the first condition does the members array contain myDoc?, you need a way to $filter the array based on the condition that satisfies the first and last properties having the same values as myDoc respectively i.e.

{
    "$filter": {
        "input": "$members",
        "cond": {
            "$and": [
                { "$eq": ["$$this.first", myDoc.first] },
                { "$eq": ["$$this.last", myDoc.last] },
            ]
        }
    }
}

Check the first element of the resulting array with $arrayElemAt

{
    "$arrayElemAt": [
        {
            "$filter": {
                "input": "$members",
                "cond": {
                    "$and": [
                        { "$eq": ["$$this.first", myDoc.first] },
                        { "$eq": ["$$this.last", myDoc.last] },
                    ]
                }
            }
        },
        0
    ]
}

If there is no match then the above will be null and we can substitute the null with a value that can be used as the main condition with $ifNull:

{
    "$ifNull": [
        {
            "$arrayElemAt": [
                {
                    "$filter": {
                        "input": "$members",
                        "cond": {
                            "$and": [
                                { "$eq": ["$$this.first", myDoc.first] },
                                { "$eq": ["$$this.last", myDoc.last] },
                            ]
                        }
                    }
                },
                0
            ]
        },
        0
    ]   
}

The above becomes basis for our condition for the first IF statement using $ne to check for inequality:

{ "$ne": [
    {
        "$ifNull": [
            {
                "$arrayElemAt": [
                    {
                        "$filter": {
                            "input": "$members",
                            "cond": {
                                "$and": [
                                    { "$eq": ["$$this.first", myDoc.first] },
                                    { "$eq": ["$$this.last", myDoc.last] },
                                ]
                            }
                        }
                    },
                    0
                ]
            },
            0
        ]   
    },
    0
] }

If the above condition is true, then the $map expression becomes

{                        
    "$map": {
        "input": "$members",
        "in": {
            "$cond": [
                { "$eq": [{ "$ifNull": ["$$this.score", 0 ] }, 0] },
                { "$mergeObjects": ["$$this", { "score": 1 } ] },
                { "$mergeObjects": ["$$this", { "score": { "$sum": ["$$this.score", 1] } } ] }
            ]
        }

    }
}

else add the new document to the existing members array with $concatArrays

{
    "$concatArrays": [
        { "$ifNull": ["$members", []] },
        [ myDoc ]
    ]
}

Your final update operation becomes:

var myDoc = { first: 'johnny', last: 'cash', score: 1 };

db.getCollection("myCollection").update(
    { "_id" : "123" },
    [
        { "$set": {
            "members": {
                "$cond": {
                    "if": {
                        "$ne": [
                            {
                                "$ifNull": [
                                    {
                                        "$arrayElemAt": [
                                            {
                                                "$filter": {
                                                    "input": "$members",
                                                    "cond": {
                                                        "$and": [
                                                            { "$eq": ["$$this.first", myDoc.first] },
                                                            { "$eq": ["$$this.last", myDoc.last] },
                                                        ]
                                                    }
                                                }
                                            },
                                            0
                                        ]
                                    },
                                    0
                                ]   
                            },
                            0
                        ]
                    },
                    "then": {
                        "$map": {
                            "input": "$members",
                            "in": {
                                "$cond": [
                                    { "$eq": [{ "$ifNull": ["$$this.score", 0 ] }, 0] },
                                    { "$mergeObjects": ["$$this", { "score": 1 } ] },
                                    { "$mergeObjects": ["$$this", { "score": { "$sum": ["$$this.score", 1] } } ] }
                                ]
                            }

                        }
                    },
                    "else": {
                        "$concatArrays": [
                            { "$ifNull": ["$members", []] },
                            [ myDoc ]
                        ]
                    }
                }
            }
        } }
    ],
    { "upsert": true } 
);