Want suggestion for update documents

Working with Data
1

Hi,

We have two collections, one is players and other is user_teams. We are updating user_teams based on points in players collection. Below is our schema structure.

// players
  [ 
    {"_id": 12592,"TotalPoints": 52},
    {"_id": 12752,"TotalPoints": 9},
    {"_id": 12605,"TotalPoints": 2},
    {"_id": 12604,"TotalPoints": -7},
    {"_id": 12770,"TotalPoints": 8},
    {"_id": 12596,"TotalPoints": 0},
    {"_id": 12764,"TotalPoints": 2},
    {"_id": 12606,"TotalPoints": 2},
    {"_id": 12755,"TotalPoints": 2},
    {"_id": 12600,"TotalPoints": 2},
    {"_id": 12599,"TotalPoints": 42},
    {"_id": 12591,"TotalPoints": 81},
    {"_id": 12756,"TotalPoints": 60},
    {"_id": 12769,"TotalPoints": -2},
    {"_id": 12610,"TotalPoints": 2}
  ]

// user_teams
  [
    {"_id": 12943,
     "Players": [ {"PlayerID": 12596,"PlayerPosition": "Player","Points": 0},
                  {"PlayerID": 12604,"PlayerPosition": "Player","Points": -7},
                  {"PlayerID": 12605,"PlayerPosition": "Player","Points": 2},
                  {"PlayerID": 12606,"PlayerPosition": "ViceCaptain","Points": 3},
                  {"PlayerID": 12608,"PlayerPosition": "Player","Points": 2},
                  {"PlayerID": 12752,"PlayerPosition": "Captain","Points": 18},
                  {"PlayerID": 12755,"PlayerPosition": "Player","Points": 2},
                  {"PlayerID": 12756,"PlayerPosition": "Player","Points": 60},
                  {"PlayerID": 12757,"PlayerPosition": "Player","Points": 2},
                  {"PlayerID": 12759,"PlayerPosition": "Player","Points": 2},
                  {"PlayerID": 12761,"PlayerPosition": "Player","Points": 97} ]},

    {"_id": 12944,
     "Players": [ {"PlayerID": 12592,"PlayerPosition": "Captain","Points": 104},
                  {"PlayerID": 12596,"PlayerPosition": "Player","Points": 0},
                  {"PlayerID": 12600,"PlayerPosition": "Player","Points": 2},
                  {"PlayerID": 12606,"PlayerPosition": "Player","Points": 2},
                  {"PlayerID": 12608,"PlayerPosition": "Player","Points": 2},
                  {"PlayerID": 12752,"PlayerPosition": "Player","Points": 9},
                  {"PlayerID": 12753,"PlayerPosition": "Player","Points": 14},
                  {"PlayerID": 12755,"PlayerPosition": "Player","Points": 2},
                  {"PlayerID": 12757,"PlayerPosition": "Player","Points": 2},
                  {"PlayerID": 12759,"PlayerPosition": "ViceCaptain","Points": 3},
                  {"PlayerID": 12764,"PlayerPosition": "Player","Points": 2} ]},

    {"_id": 12945,
     "Players": [ {"PlayerID": 12591,"PlayerPosition": "Player","Points": 81},
                  {"PlayerID": 12599,"PlayerPosition": "Player","Points": 42},
                  {"PlayerID": 12605,"PlayerPosition": "ViceCaptain","Points": 3},
                  {"PlayerID": 12610,"PlayerPosition": "Player","Points": 2},
                  {"PlayerID": 12753,"PlayerPosition": "Captain","Points": 28},
                  {"PlayerID": 12755,"PlayerPosition": "Player","Points": 2},
                  {"PlayerID": 12756,"PlayerPosition": "Player","Points": 60},
                  {"PlayerID": 12757,"PlayerPosition": "Player","Points": 2},
                  {"PlayerID": 12759,"PlayerPosition": "Player","Points": 2},
                  {"PlayerID": 12760,"PlayerPosition": "Player","Points": 0},
                  {"PlayerID": 12770,"PlayerPosition": "Player","Points": 8} ]},

    {"_id": 12946,
     "Players": [ {"PlayerID": 12591,"PlayerPosition": "Player","Points": 81},
                  {"PlayerID": 12599,"PlayerPosition": "Player","Points": 42},
                  {"PlayerID": 12605,"PlayerPosition": "ViceCaptain","Points": 3},
                  {"PlayerID": 12610,"PlayerPosition": "Player","Points": 2},
                  {"PlayerID": 12753,"PlayerPosition": "Captain","Points": 28},
                  {"PlayerID": 12755,"PlayerPosition": "Player","Points": 2},
                  {"PlayerID": 12756,"PlayerPosition": "Player","Points": 60},
                  {"PlayerID": 12757,"PlayerPosition": "Player","Points": 2},
                  {"PlayerID": 12759,"PlayerPosition": "Player","Points": 2},
                  {"PlayerID": 12760,"PlayerPosition": "Player","Points": 0},
                  {"PlayerID": 12770,"PlayerPosition": "Player","Points": 8} ]}
  ]

Right now we are updating points by fetching all players and update user_teams player’s points one by one using loop.

  $UserTeams = $this->db->{'user_teams'};
  $Players = $this->db->{'players'};
  $PlayersData = $Players->find();
  
  $ViceCaptainMultiplier = 1.5;
  $CaptainMultiplier = 2;

  foreach ($PlayersData as $Player) {
    $TotalPoints = $Player['TotalPoints'];
    $PlayerID = $Player['_id'];
 
    $UserTeams->updateMany(
        [ 'Players.PlayerID' => $PlayerID ],
        [ '$set' => [ 'Players.$[c].Points' => $TotalPoints * $CaptainMultiplier,
                      'Players.$[vc].Points' => $TotalPoints * $ViceCaptainMultiplier,
                      'Players.$[p].Points' => $TotalPoints
                    ]
        ],
        [ 'arrayFilters' => [ [ "c.PlayerPosition" => "Captain", "c.PlayerID" => $PlayerID ],
                              [ "vc.PlayerPosition" => "ViceCaptain", "vc.PlayerID" => $PlayerID ],
                              [ "p.PlayerPosition" => "Player", "p.PlayerID" => $PlayerID ]
                            ]
        ]
    );
 };

Now I want to know that, Is this correct way to do this? Or can I do this by any other way like using aggregation. Any possibility to do this without loop? Because number of documents can be in millions.

Thanks.

2

Hello @Dharmesh_Prajapati, welcome to the MongoDB Community forum!

The usage of for-loop to fetch each player and update corresponding user_teams document(s) is okay. Only, the update statement may not work (have you tried your code with some sample data?) as you are intending it to. The arrayFilters condition is using the implicit and (I think it needs to be an $or operator).

Yes, you can do this with a Updates with Aggregation Pipeline - but, you cannot avoid the initial fetch from the players collection and the usage of the for-loop.

To update a large number of documents efficiently, use Bulk Writes.

4

Hi @Prasad_Saya

Thank you very much for your reply.

The usage of for-loop to fetch each player and update corresponding user_teams document(s) is okay.

To update user_teams collection from the player, Is it possible to use $merge operator of aggregation?

Only, the update statement may not work (have you tried your code with some sample data?) as you are intending it to. The arrayFilters condition is using the implicit.

Yes, everything is working fine. The only concern is to avoid the use of for-loop.

To update a large number of documents efficiently, use Bulk Writes.

Sure, I will try bulk writer.

5

I don’t know if it can be used in your case; see the way $merge Aggregation Pipeline Stage works.