Add new field to a MongoDB document parsing from String to Int using updateMany

Question

In my MongoDB collection, all documents contain a mileage field which currently is a string. Using PHP, I'd like to add a second field which contains the same content, but as an integer value. Questions like How to change the type of a field? contain custom MongoDB code which I don't want to run using PHP, and questions like mongodb php Strings to float values retrieve all documents and loop over them.

Is there any way to use \MongoDB\Operation\UpdateMany for this, as this would put all the work to the database level? I've already tried this for static values (like: add the same string to all documents), but struggle with getting the data to be inserted from the collection itself.

Some further hints:

I'm looking for a pure PHP solution that does not rely on any binary to be called using exec. This should avoid installing more packages than needed on the PHP server
Currently, I have to use MongoDB in v4.0. Yes, that's not the most recent version, but I'm not in the position to perform an upgrade

Also, take a look at this post: [Update MongoDB field using value of another field](https://stackoverflow.com/questions/3974985/update-mongodb-field-using-value-of-another-field). — prasad_, Oct 29 '19 at 13:46
@prasad_ is that relevant? Currently, `mongodb/mongodb` is installed in v1.4.3 — Nico Haase, Oct 29 '19 at 13:46
@prasad_ thanks for that hint, but the other answers do not cover any implementation in PHP. I don't want to use `exec` or other ways that assume that a shell client for MongoDB is installed — Nico Haase, Oct 29 '19 at 13:47
I believe you could use the mongodb aggregation pipeline $merge stage assuming you are using mongodb 4.2. I believe aggregation pipeline is available to PHP clients. — barrypicker, Oct 29 '19 at 15:27

s7vr · Answer 1 · 2019-10-31T15:06:22.827

1

You could use $set like this in 4.2 which supports aggregation pipeline in update.

$set stage creates a mileageasint based on the previous with $toInt value

db.collection.updateMany(
   <query>,
   [{ $set: { "mileageasint":{"$toInt":"$mileage" }}}],
   ...
)

Php Solution ( Using example from here)

$updateResult = $collection->updateMany(
    [],
    [['$set' => [ 'mileageasint' =>  [ '$toInt' => '$mileage']]]]
);

edited Oct 31 '19 at 15:06

answered Oct 31 '19 at 14:08

s7vr

73,656
11
106
127

Can you explain how to use this in PHP context? – Nico Haase Oct 31 '19 at 14:08
The given code can not work in PHP, as double quotes around a string with a `$` would use a variable. The variable `$toInt` is not defined. By using single quotes, the following error is thrown: `First key in $update argument is not an update operator` – Nico Haase Oct 31 '19 at 14:56
When skipping one pair of brackets around the `$set` array, the following exception is thrown: `The dollar ($) prefixed field '$toInt' in 'mileageasint.$toInt' is not valid for storage.` – Nico Haase Oct 31 '19 at 14:58
Thank you. I will fix the quotes. The first error means you are not on the latest version (4.2). Also need to upgrade your php driver to 1.5.0 to support changes in 4.2 version. – s7vr Oct 31 '19 at 15:05
Okay, that's something I had to check first. Currently, I have to use MongoDB 3.4 - yes, I know, that's a pretty old version, but upgrading it is not under my control :( – Nico Haase Oct 31 '19 at 15:13
Latest news: I may use v4.0, but that still does not perform this `$set` call :( – Nico Haase Oct 31 '19 at 15:25
1

In older version - You could use bulk update to add the new field or use aggregation stage $out to recreate the collection with new field. – s7vr Oct 31 '19 at 15:34

score 1 · Accepted Answer · answered Oct 31 '19 at 15:45

Try this, please:

01) MongoDB Aggregate reference:

db.collectionName.aggregate(
    [
        { "$addFields": { 
            "intField": { "$toInt": "$stringFieldName" } 
        }},
        { "$out": "collectionName" }
    ]
)

02) Possible PHP solution (Using as reference https://www.php.net/manual/en/mongocollection.aggregate.php):

$pipeline = array(
    array(
        '$addFields' => array(
            'integerField' => array('$toInt' => '$mileage')
        )
    ),
    array(
        '$out' => 'collection'        
    ),
);
$updateResult = $collection->aggregate(pipeline);

Thanks Haruo, that's a working solution! Just minutes ago, I came up with a similar one — Nico Haase, Oct 31 '19 at 15:51

Add new field to a MongoDB document parsing from String to Int using updateMany

2 Answers2