Increment value in document and nested array simultaenously (with upsert)

Question

I want to increment a value both on document root as well as inside a nested array.

A playground example here

The schema

const UserPoints = new Schema(
    {
        points: {
            type: Number,
        },
        monthly: {
            type: [
                new Schema(
                    {
                        year: {
                            type: Number,
                        },
                        month: {
                            type: Number,
                        },
                        points: {
                            type: Number,
                            min: 0,
                        },
                    },
                    {
                        _id: false,
                        timestamps: false,
                    }
                ),
            ],
        },
    },
    {
        timestamps: false,
    }
);

What I have tried
Variables used: (currentYear = 2021, currentMonth = 7, addPoints = 5)
Note: The year, month may not exist in the document yet, so I need to make it work with "upsert".

UserPoints.findOneAndUpdate(
    {
        _id: userId,
        "monthly.year": currentYear,
        "monthly.month": currentMonth,
    },
    {
        $inc: {
            points: addPoints,
            "monthly.$.points": addPoints,
        },
    },
    {
        upsert: true,
        new: true,
    }
).exec()

This does not work. And gives out an error:

{
ok:0
code:2
codeName:"BadValue"
name:"MongoError"
}

I would appreciate if someone can point me at the right direction to increment these values in the same operation.

---

Update: The only way that I could make it work is by first making a query to check if the (year, month) exists in the "monthly" array. Depending if it exists, I either $push the new month, or $inc the existing one's values.

Answer 1

Pipeline update way, requires MongoDB >=4.2
You filter by _id using index, so it will be fast also.

Query

Test code here
(put id=1 will update(add points to member points and to externa point field), id=2 will do nothing,id=3 will insert the member to the empty array and update the points, id=4 will upsert with an array contains only this member,and the points its points)

Cases (this is the order checked in the $cond also)

• document doesnt exists, array will have 1 single member, and root points will have the value of the new member points
• document exists , member dont exists, monthly empty adds the member {:year ...} and increases the root point field
• document exists, member exists increase points inside the member and the root point fields
• document exists, member dont exist, monthly not empty does nothing

db.collection.update({
  "_id": 4
},
[
  {
    "$addFields": {
      "isupsert": {
        "$not": [
          {
            "$ne": [
              {
                "$type": "$monthly"
              },
              "missing"
            ]
          }
        ]
      }
    }
  },
  {
    "$addFields": {
      "doc": {
        "$switch": {
          "branches": [
            {
              "case": "$isupsert",
              "then": {
                "_id": "$_id",
                "points": 5,
                "monthly": [
                  {
                    "year": 2021,
                    "month": 7,
                    "points": 5
                  }
                ]
              }
            },
            {
              "case": {
                "$and": [
                  {
                    "$isArray": [
                      "$monthly"
                    ]
                  },
                  {
                    "$eq": [
                      {
                        "$size": "$monthly"
                      },
                      0
                    ]
                  }
                ]
              },
              "then": {
                "_id": "$_id",
                "points": 5,
                "monthly": [
                  {
                    "year": 2021,
                    "month": 7,
                    "points": 5
                  }
                ]
              }
            }
          ],
          "default": {
            "$let": {
              "vars": {
                "found": {
                  "$not": [
                    {
                      "$eq": [
                        {
                          "$size": {
                            "$filter": {
                              "input": "$monthly",
                              "as": "m",
                              "cond": {
                                "$and": [
                                  {
                                    "$eq": [
                                      "$$m.year",
                                      2021
                                    ]
                                  },
                                  {
                                    "$eq": [
                                      "$$m.month",
                                      7
                                    ]
                                  }
                                ]
                              }
                            }
                          }
                        },
                        0
                      ]
                    }
                  ]
                }
              },
              "in": {
                "$cond": [
                  "$$found",
                  {
                    "$mergeObjects": [
                      "$ROOT",
                      {
                        "points": {
                          "$add": [
                            "$points",
                            5
                          ]
                        },
                        "monthly": {
                          "$map": {
                            "input": "$monthly",
                            "as": "m",
                            "in": {
                              "$cond": [
                                {
                                  "$and": [
                                    {
                                      "$eq": [
                                        "$$m.year",
                                        2021
                                      ]
                                    },
                                    {
                                      "$eq": [
                                        "$$m.month",
                                        7
                                      ]
                                    }
                                  ]
                                },
                                {
                                  "$mergeObjects": [
                                    "$$m",
                                    {
                                      "points": {
                                        "$add": [
                                          "$$m.points",
                                          5
                                        ]
                                      }
                                    }
                                  ]
                                },
                                "$$m"
                              ]
                            }
                          }
                        }
                      }
                    ]
                  },
                  "$$ROOT"
                ]
              }
            }
          }
        }
      }
    }
  },
  {
    "$replaceRoot": {
      "newRoot": "$doc"
    }
  },
  {
    "$unset": [
      "isupsert"
    ]
  }
],
{
  "upsert": true
})

Answer 2

you should find in array by $elemMatch

UserPoints.findOneAndUpdate(
    {
        _id: userId,
        "monthly":{$elemMatch:{"year": currentYear}},
        "monthly":{$elemMatch:{"month": currentMonth}}
    },
    {
        $inc: {
            points: addPoints,
            "monthly.$.points": addPoints,
        },
    },
    {
        upsert: true,
        new: true,
    }
).exec()