MongoDB: Using map-reduce to change string into integer

Question

I am new here and asking a question for a mongodb related problem I have been facing.

Here is an example structure of how one record would look like.

{
    "_id" : {
             "id" : "lk23j",
             "language" : "English"
            },
   "class" : "test",
   "title" : {
              "duration" : "34"
              "year" : "1991"
             }
}

There are several records like this and I would like to take the sum of duration, in all records. To my understanding aggregation would not work as title.duration field needs to be converted to integer, so taking the sum returns NaN(not a number). Hence I'll need to convert then take the sum using map-reduce which will allow parseInt(in javascript).

From http://blog.physalix.com/datas-manipulation-in-mongodb-rename-field-change-type-add-sub-document/, I was unable to change the title.duration to integer:

db.members.find().forEach( function (x) {x.title.duration= parseInt(x.title.duration);});

The query above threw a

"cannot read property 'duration' of undefined".

I frequently get the error

Invalid left-hand side in assinment" for "title.duration"= parseInt("title.duration");

I've tried a similar approach using map-reduce but had no success. I am having troubles referencing the data title.duration.

If someone can help out I'd greatly appreciate it!

Answer 1

You could try filtering your collection for documents where the title.duration field is of string type and exists, then iterate over the find() cursor with the forEach method, do the conversion and save the updated document.

Let's take the minimum test case and insert the following test documents to a test collection:

db.test.insert([
    {
        "_id" : {
            "id" : "lk23j",
            "language" : "English"
        },
        "class" : "test",
        "title" : {
            "duration" : "34",
            "year" : "1991"
        }
    },
    {
        "_id" : {
            "id" : "abc",
            "language" : "French"
        },
        "class" : "foo"
    },
    {
        "_id" : {
            "id" : "def",
            "language" : "German"
        },
        "class" : "bar",
        "title" : {
            "year" : "1991"
        }
    },
    {
        "_id" : {
            "id" : "erb42",
            "language" : "Shona"
        },
        "class" : "xyz",
        "title" : {
            "duration" : null,
            "year" : "1993"
        }
    },
    {
        "_id" : {
            "id" : "urn321",
            "language" : "Latin"
        },
        "class" : "bar",
        "title" : {
            "duration" : "",
            "year" : "1999"
        }
    }
])

The conversion operation as described above can then be carried out as follows:

db.test.find({ "title.duration": { "$type" : 2 } }).forEach(function (doc){ 
    doc.title.duration = parseInt(doc.title.duration) || 0;
    db.test.save(doc);
});

After the conversion you can then use the aggregation framework to calculate the sum on the title.duration field for all documents as follows:

db.test.aggregate([
    {
        "$group": {
            "_id": null,
            "total_duration": {
                "$sum": "$title.duration"
            }
        }
    }
]);

And the result:

/* 0 */
{
    "result" : [ 
        {
            "_id" : null,
            "total_duration" : 34
        }
    ],
    "ok" : 1
}