MongoDB - Update record if 'date' exists, otherwise add a new one

Question

I've looked at similar questions but couldn't find my answer. I've got this info:

{
  "_id": "631337640754675725",
  "characters": [
    {
      "name": "Druuwu",
      "scores": [
        {
          "date": "2023-09-03",
          "score": 100
        },
        {
          "date": "2023-09-03",
          "score": 1000
        },
        {
          "date": "2023-09-03",
          "score": 101
        },
        {
          "date": "2023-09-03",
          "score": 102
        }
      ]
    },
  ]
}

And I'm updating/adding using this logic here:

    await culvertSchema.findOneAndUpdate(
      {
        _id: interaction.user.id,
        "characters.name": selectedCharacter,
      },
      {
        $addToSet: {
          "characters.$[index].scores": { score: culvertScore, date: reset },
        },
      },
      {
        arrayFilters: [{ "index.name": selectedCharacter }],
        new: true,
      }
    );

How could I make it so that if a score with the same 'date' already exists, it would just update it with a new score? Instead of creating a new record with the same date and different score. Thanks!

Answer 1

Consider refactoring your schema to characters level like this if possible. Most of the complexity of your case comes from handling doubly nested array.

db.collection.aggregate([
  {
    $match: {
      "_id": "631337640754675725",
      "characters.name": "Druuwu"
    }
  },
  {
    "$unwind": "$characters"
  },
  {
    // update the scores array first if matched
    $set: {
      "characters.scores": {
        "$map": {
          "input": "$characters.scores",
          "as": "s",
          "in": {
            "$cond": {
              "if": {
                $eq: [
                  "2023-09-03",
                  "$$s.date"
                ]
              },
              "then": {
                "date": "2023-09-03",
                "score": -1
              },
              "else": "$$s"
            }
          }
        }
      }
    }
  },
  {
    // append the element if not already updated
    $set: {
      "characters.scores": {
        "$cond": {
          "if": {
            $in: [
              {
                "date": "2023-09-03",
                "score": -1
              },
              "$characters.scores"
            ]
          },
          "then": "$characters.scores",
          "else": {
            "$concatArrays": [
              "$characters.scores",
              [
                {
                  "date": "2023-09-03",
                  "score": -1
                }
              ]
            ]
          }
        }
      }
    }
  },
  // regroup unwinded characters documents
  {
    "$group": {
      "_id": "$_id",
      "characters": {
        "$push": "$characters"
      }
    }
  },
  {
    "$merge": {
      "into": "collection",
      "on": "_id"
    }
  }
])

Mongo Playground