Need help to write update query

Working with Data
,
1

Hi

I have my structure like this

[
{
"_id": 12175,
"MatchID": 11978,
"Players": [
{
"PlayerID": 12063,
"PlayerPosition": "Captain",
"Points": 8
},
{
"PlayerID": 12041,
"PlayerPosition": "Player",
"Points": 3
},
{
"PlayerID": 12066,
"PlayerPosition": "Player",
"Points": 21
},
{
"PlayerID": 12067,
"PlayerPosition": "Player",
"Points": 33
},
{
"PlayerID": 12064,
"PlayerPosition": "Player",
"Points": 0
},
{
"PlayerID": 12069,
"PlayerPosition": "ViceCaptain",
"Points": 12288
},
{
"PlayerID": 12045,
"PlayerPosition": "Player",
"Points": 0
},
{
"PlayerID": 12074,
"PlayerPosition": "Player",
"Points": -3
},
{
"PlayerID": 12079,
"PlayerPosition": "Player",
"Points": 8
},
{
"PlayerID": 12059,
"PlayerPosition": "Player",
"Points": 0
},
{
"PlayerID": 12054,
"PlayerPosition": "Player",
"Points": 0
}
],
"Points": 3141
}
]

Now I am trying to update the player’s point using the below query

db.user_teams.updateMany(
{"MatchID": 11978, "Players.PlayerID": 12063},

    {
        $set: { 'Players.$.Points' : { $switch: {
                       branches: [
                           { case: { $eq: [ "$Players.$.PlayerPosition", "Captain" ] }, then: 16 },
                           { case: { $eq: [ "$Players.$.PlayerPosition", "ViceCaptain" ] }, then: 12 }
                       ],
                       default: 8
            } } }
    }
);

But this is giving me error
com.mongodb.MongoWriteException: The dollar ($) prefixed field ‘$switch’ in ‘Players.0.Points.$switch’ is not valid for storage.

I also tried with

db.user_teams.updateMany(
{"MatchID": 11978, "Players.PlayerID": 12063},
[
{
$set: { 'Players.$.Points' : { $switch: {
branches: [
{ case: { $eq: [ "$Players.$.PlayerPosition", "Captain" ] }, then: 16 },
{ case: { $eq: [ "$Players.$.PlayerPosition", "ViceCaptain" ] }, then: 12 }
],
default: 8
} } }
}
]
);

Getting error
com.mongodb.MongoWriteException: Invalid set :: caused by :: FieldPath field names may not start with ''.

Please help me to resolve this error.

1 Like
2

The $switch is a aggregation pipeline operator, regular update query will not allow to use this operation,

There is a option update with aggregation pipeline starting from MongoDB 4.2,

  • $map to iterate loop of Players array, check condition ($cond) if PlayerID match then do $switch operation else return same object
  • $mergeObjects to merge current object with updated Points field
db.user_teams.updateMany({
  "MatchID": 11978,
  "Players.PlayerID": 12063
},
[
  {
    $set: {
      Players: {
        $map: {
          input: "$Players",
          in: {
            $mergeObjects: [
              "$$this",
              {
                $cond: [
                  { $eq: ["$$this.PlayerID", 12063] },
                  {
                    Points: {
                      $switch: {
                        branches: [
                          {
                            case: { $eq: ["$$this.PlayerPosition", "Captain"] },
                            then: 16
                          },
                          {
                            case: { $eq: ["$$this.PlayerPosition", "ViceCaptain"] },
                            then: 12
                          }
                        ],
                        default: 8
                      }
                    }
                  },
                  "$$this"
                ]
              }
            ]
          }
        }
      }
    }
  }
])

Playground

You can use other option, arrayFilters $[identifier],

  • Create three filters, first for Position Captain second for Position ViceCaptain and Third for Position not in Captain, ViceCaptain,
db.collection.updateMany({
  "MatchID": 11978,
  "Players.PlayerID": 12063
},
{
  $set: {
    "Players.$[c].Points": 16,
    "Players.$[v].Points": 12,
    "Players.$[cv].Points": 8
  }
},
{
  arrayFilters: [
    {
      "c.PlayerPosition": "Captain",
      "c.PlayerID": 12063
    },
    {
      "v.PlayerPosition": "ViceCaptain",
      "v.PlayerID": 12063
    },
    {
      "cv.PlayerPosition": {
        $nin: [
          "Captain",
          "ViceCaptain"
        ]
      },
      "cv.PlayerID": 12063
    }
  ]
})

Playground

3

Hi @turivishal

Thank you very much. Both methods are working fine.

But which technique is preferable and optimized?

Once again thank you.

1 Like
4

I would prefer second method arrayFilters because it will update in exact matching element,
I would not prefer first aggregation method because it process for whole array and write again so it may impact performance.

5

Great, Thank you for your very quick response. I appreciate your investigation.

6

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.