Update single (same) field in every object of an array

Dan_Burt · June 16, 2021, 8:10am

I have the following document structure. I am trying to update specific values inside the holes sub-array:

Each Array element is an Object representing the score on a golf hole, with various properties (fields). I am trying to provide the ability to update the holeGross field ONLY for each score. On my PHP website, this is using a POST form, which pushes an Array of scores and the round._id value too, as:

Array ( [holeScoreHidden] => Array ( [1] => 7 [2] => 7 [3] => 7 [4] => 8 [5] => 7 [6] => 7 [7] => 7 [8] => 7 [9] => 7 ) [roundId] => 60c642db09080f1b50331b2d [submit] => )

However, initially I am trying to get it working with MongoDB shell syntax. I have tried following the findAndModify() method in the MongoDB shell, which updates 1 field specified, i.e. where hole.no == 1:

db.roundTest.findAndModify({
   query: { _id : ObjectId('60c916684bd16901f36efb3a') },
   update: { $set: { "holes.$[elem].holeGross" : 8 } },
   arrayFilters: [ { "elem.no": 1 } ]
})

How can I update all Array elements in this document? Each holeGross to be updated will [likely] be different, so need to match the hole.no (“elem.no”) against the corresponding new score.

I expect I will need to also perform a loop, in order to go through the $_POST array. Would this likely be to form echo statements, or can it be directly integrated into the findAndReplace call?

Also, can I perform this within a single method call, or would this approach require a call per single field / document that is to be updated? I would prefer a single call, obviously.

The documentation says that arrayFilters is:

arrayFilters: [ <filterdocument1>, ... ]

But I don’t want to pass in a whole document.

Prasad_Saya · June 16, 2021, 10:17am

Hello @Dan_Burt, you can use the Updates with Aggregation Pipeline feature to update different values of holeGross for all the elements of the holes array.

Suppose you have a document with two holes, for example:

{
        "_id" : 1,
        "player" : "John",
        "holes" : [
                {
                        "no" : 1,
                        "par" : 3,
                        "holeGross" : 2
                },
                {
                        "no" : 2,
                        "par" : 4,
                        "holeGross" : 3
                }
        ]
}

and the new values of hole gross in an array, for example:

var new_vals = [ 9, 5 ]

The following update operation will change each element of the array with new values (in your case, you need to supply an array of 8 elements as there are eight holes) in a single call.

db.collection.updateOne(
  { _id: 1 },
  [
    {
      $set: {
          holes: {
              $map: {
                  input: { $range: [ 0, { $size: "$holes" } ] },
                  in: {
                      $mergeObjects: [ 
                          { $arrayElemAt: [ "$holes", "$$this"  ] }, 
                          { holeGross: { $arrayElemAt: [ new_vals, "$$this"  ] } } 
                      ]
                  }
              }
          }
       }
    }
  ]
)

Dan_Burt · June 16, 2021, 8:35pm

Thanks, I have tested that this works as I expect in the MongoDB shell.

Was the findAndModify / findAndReplace methods the incorrect approach for what I was attempting? Is that only to update a single field, and not multi-dimensional arrays of stuff?

Dan_Burt · June 16, 2021, 9:07pm

Also, are these Aggregation methods available in PHP as well?

Prasad_Saya · June 17, 2021, 4:57am

@Dan_Burt , the findAndModify also has the option to use the Aggregation Updates - so it will also work in the same way. The findAndReplace has no such option.

I see that the update methods (updateOne and findOneAndUpdate) of the MongoDB PHP Driver supports the Updates with Aggregation Pipeline feature:

Dan_Burt · June 17, 2021, 8:31am

So I tried retaining the existing code, formatted for PHP, i.e. replacing “:” with “=>” and using square braces.

It returns:

**Fatal error** : Uncaught MongoDB\Driver\Exception\BulkWriteException: Unknown modifier: $map. Expected a valid update modifier or pipeline-style update specified as an array in /var/www/html/vendor/mongodb/mongodb/src/Operation/Update.php:228 Stack trace: #0 /var/www/html/vendor/mongodb/mongodb/src/Operation/Update.php(228): MongoDB\Driver\Server->executeBulkWrite('golf.roundTest', Object(MongoDB\Driver\BulkWrite), Array) #1 /var/www/html/vendor/mongodb/mongodb/src/Operation/UpdateOne.php(117): MongoDB\Operation\Update->execute(Object(MongoDB\Driver\Server)) #2 /var/www/html/vendor/mongodb/mongodb/src/Collection.php(1075): MongoDB\Operation\UpdateOne->execute(Object(MongoDB\Driver\Server)) #3 /var/www/html/functions.php(783): MongoDB\Collection->updateOne(Array, Array) #4 /var/www/html/updateRound.php(19): setRoundScores('60cb07d14bd1690...', Array) #5 {main} thrown in **/var/www/html/vendor/mongodb/mongodb/src/Operation/Update.php** on line **228**

My guess is $map isn’t available to the PHP drivers?

The syntax in use is:

function setRoundScores($roundId, $scoresArray) {
    	
	$collection = $client->golf->roundTest;
		
    $match = [
    	'$match' => [
    		'_id' => new MongoDB\BSON\ObjectID( $roundId )
    	]
    ];
    	
    $set = [
    	'$map' => [
    		'input' => [
    			'$range' => [ 0, [ '$size' => '$holes']],
    			'in' => [
    				'$mergeObjects' => [
    					[ '$arrayElemAt' => [ '$holes', '$$this']],
    					[ 'holeGross' => [ '$arrayElemAt' => $scoresArray, '$$this']]
    				]
    			]
    		]
    	]
    ];
    	
    $updateOne = $collection->updateOne($match,$set);
    	
	return $updateOne->getUpsertedId();
		
}

Prasad_Saya · June 17, 2021, 8:56am

Hello @Dan_Burt, are you trying to use $match in the aggregate update (match is not valid stage for update)? You need to use regular query filter.

I am not familiar with PHP programming. Here is some syntax using $map in PHP:

Dan_Burt · June 17, 2021, 9:19am

But the error is not complaining about that stage. Copying from previous PHP statements, I used the '$match' construct, as thought it aligned to the first $filter parameter supplied to the updateOne() method call.

I have tried replacing the 1st stage with this, which looks more like the format in the documentation link you provided for the PHP driver:

$match2 = [ '_id' => new MongoDB\BSON\ObjectID( $roundId ) ];

I just prefer to split these out to separate var’s for readability.

And it still ONLY errors about the '$map' stage. Maybe I need to pursue this on Stack Overflow, that might have more PHP-savvy developers to assist.

Dan_Burt · June 17, 2021, 9:21am

Or would a simpler (but NOT efficient, I know) method be to replace the entire holes sub-array in the updateOne() call?

This would mean sending over loads more data than is necessary… But I don’t get caught up in advanced Aggregation pipelines.

Newbie, just enquiring about options and fallbacks…

Prasad_Saya · June 17, 2021, 9:34am

@Dan_Burt ,

The way you are coding the update in PHP may not be correct, I think.

The following is the mongo shell version from my post, where $set is the aggregation stage, holes is the array field being updated, and the $map is the aggregate operator:

$set: {
      holes: {
            $map: { ...

But, in your PHP version, I only see this:

'$map' => [ ...

As such PHP MongoDB Driver (latest versions) support the Update with the Aggregate Pipeline along with MongoDB server v4.2 or later.

[ Post Updated ]:

I think your update should be something like this in PHP:

[
 "$set" => [
    "holes" => [
        "$map" => [
             "input" => [ ... ],
             "in" => [...]"
        ]
    ]
  ]
]

This corresponds to the shell update’s code:

  [
    {
      $set: {
          holes: {
              $map: {
                  input: { $range: [ 0, { $size: "$holes" } ] },
                  in: {
                      $mergeObjects: [ 
                          { $arrayElemAt: [ "$holes", "$$this"  ] }, 
                          { holeGross: { $arrayElemAt: [ new_vals, "$$this"  ] } } 
                      ]
                  }
              }
          }
       }
    }
  ]

And, the filter would be : ['_id' => 'some value ...']

Dan_Burt · June 17, 2021, 10:05am

Yes, I think you are correct - the syntax gets very tricky very quickly!

I have a new error code now, having tried to correct the structure:

$set = [
    '$set' => [
    	'holes' => [
    		'$map' => [
    			'input' => [
    				'$range' => [ 0, [ '$size' => '$holes']]
    			],
				'in' => [
					'$mergeObjects' => [
						[ '$arrayElemAt' => [ '$holes', '$$this' ]],
						[ 'holeGross' => [ '$arrayElemAt' => $scoresArray, '$$this' ]]
					]
				]
    					
    		]
    	]
    ]
];

Errors with:

Fatal error : Uncaught MongoDB\Driver\Exception\BulkWriteException: The dollar ($) prefixed field ‘$map’ in ‘holes.$map’ is not valid for storage. in /var/www/html/vendor/mongodb/mongodb/src/Operation/Update.php:228 Stack trace: #0 /var/www/html/vendor/mongodb/mongodb/src/Operation/Update.php(228): MongoDB\Driver\Server >executeBulkWrite(‘golf.roundTest’, Object(MongoDB\Driver\BulkWrite), Array) #1 /var/www/html/vendor/mongodb/mongodb/src/Operation/UpdateOne.php(117): MongoDB\Operation\Update->execute(Object(MongoDB\Driver\Server)) #2 /var/www/html/vendor/mongodb/mongodb/src/Collection.php(1075): MongoDB\Operation\UpdateOne >execute(Object(MongoDB\Driver\Server)) #3 /var/www/html/functions.php(804): MongoDB\Collection >updateOne(Array, Array) #4 /var/www/html/updateRound.php(19): setRoundScores(‘60cb07d14bd1690…’, Array) #5 {main} thrown in /var/www/html/vendor/mongodb/mongodb/src/Operation/Update.php on line 228

Prasad_Saya · June 17, 2021, 10:21am

@Dan_Burt, I think the feature to update using the pipeline is still being implemented with the PHP driver.

See this JIRA: https://jira.mongodb.org/browse/DRIVERS-626

An alternative is to use the “execute database command” to run the update command:

Prasad_Saya · June 17, 2021, 10:51am

From the execute basic command in MongoDB PHP, I think your update can be like this:

$cursor = $database->command([
      'update': 'collection_name',
      'updates': [
        [
           'q': [ '_id' => 1 ],
           'u': [
               [...]          // <-------- see the code below to fill (the brackets stay)
           ]
         ],
      ],
   ]
]);

$results = $cursor->toArray()[0];
var_dump($results);

This code goes into the ... of the above command:

 "$set" => [
    "holes" => [
        "$map" => [
             "input" => [ ... ],
             "in" => [...]"
        ]
    ]
  ]

Dan_Burt · June 18, 2021, 5:41pm

Received assistance on Stack Overflow. Providing the final working syntax for future use:

function setRoundScores($roundId, $scoresArray) {
    	
    $client = new MongoDB\Client($_ENV['MDB_CLIENT']);

	$collection = $client->golf->round;

    $match = [ '_id' => new MongoDB\BSON\ObjectID( $roundId ) ];
    	
    $set = [
    	'$set' => [
    		'holes' => [
    			'$map' => [
    				'input' => [
    					'$range' => [ 0, [ '$size' => '$holes']]
    				],
					'in' => [
						'$mergeObjects' => [
							[ '$arrayElemAt' => [ '$holes', '$$this' ]],
							[ 'holeGross' => [ '$toInt' => [ '$arrayElemAt' => [ $scoresArray, '$$this' ]]]]
						]
					]
    					
    			]
    		]
    	]
    ];
    	
    $updateOne = $collection->updateOne($match, [$set]);
    	
	return $updateOne->getModifiedCount();
		
}

It was the number of square braces in the 'holeGross' => line. I also had to add an extra layer to this, '$toInt', as passing the var’s using $_POST converted them to strings.

system · June 23, 2021, 5:42pm

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