Convert MongoDB field from String to ISODate in array

Question

I need to change the type of stored array values from String to ISODate in MongoDB. The process should change the stored types, not just project them out in a new format.

The document structure is as follows with the target values nested in an array at absences[].date.

[{
    "id": 3086,
    "first": "Knox",
    "last": "Keith",
    "middle": "Kent",
    "absences": [{
            "date": "Wed Nov 28 2018 15:12:09 GMT+0000 (UTC)",
            "code": "X",
            "type": "E",
            "isPartial": false
        },
        {
            "date": "Wed Dec 26 2018 12:35:46 GMT+0000 (UTC)",
            "code": "X",
            "type": "E",
            "isPartial": false
        }
    ]
}]

I can change the value of those fields (but not the type) easily with $set:

db.students.update(
   { },
   { $set: { "absences.$[].date" : "changed" } },
   { multi: true }
)

@JohnnyHK shared this example of changing a String to ISODate, but this only works for top-level objects (not arrays).

db.snippets.find({created_at: {$not: {$type: 9}}}).forEach(function(doc) {
    doc.created_at = new Date(doc.created_at);
    db.snippets.save(doc);
})

I'd be grateful for advice about combining these two strategies, i.e. looping through the absences array to convert the date field from String to ISODate.

Mani · Accepted Answer · 2019-03-19T02:48:31.453

1

This can be achieved using the below aggregation pipeline.

db.students.aggregate([
    {
        '$addFields': {
            'absences': {
                '$map': {
                    'input': '$absences', 
                    'as': 'absence', 
                    'in': {
                        'date': {
                            '$toDate': {
                                '$substr': [
                                    '$$absence.date', 0, {
                                        '$subtract': [
                                            {
                                                '$strLenCP': '$$absence.date'
                                            }, 5
                                        ]
                                    }
                                ]
                            }
                        }, 
                        'code': '$$absence.code', 
                        'type': '$$absence.type', 
                        'isPartial': '$$absence.isPartial'
                    }
                }
            }
        }
    }, {
        '$out': 'students'
    }
])

edited Mar 19 '19 at 02:48

answered Mar 18 '19 at 06:09

Mani

1,471
1
13
19

As I understand it, [$addFields](https://docs.mongodb.com/manual/reference/operator/aggregation/addFields/) only projects out the data in a different format. It does not change the underlying [BSON value type](https://docs.mongodb.com/manual/reference/bson-types/). – It'sNotMe Mar 18 '19 at 07:39
Check the `$out` stage at the end. $out stage, Takes the documents returned by the aggregation pipeline and writes them to a specified collection. If the collection specified by the $out operation already exists, then upon completion of the aggregation, the $out stage atomically replaces the existing collection with the new results collection. https://docs.mongodb.com/manual/reference/operator/aggregation/out/ – Mani Mar 18 '19 at 15:41
I have fixed the date conversion issue, the query should work for you. – Mani Mar 19 '19 at 02:48
Thank you for this solution, and for educating me about $out. – It'sNotMe Mar 19 '19 at 20:30
Though date fields are now in ISODate() format, there are variable changes to UTC offsets. Notice for the two fields below there is a -0700 offset and a -0800 offset. Do you have any thoughts about why that happened? { "date" : ISODate("2019-01-28T16:18:42.000-08:00"), "code" : "X", "type" : "E", "isPartial" : false }, { "date" : ISODate("2019-03-14T08:25:53.000-07:00"), "code" : "U", "type" : "U", "isPartial" : false } – It'sNotMe Mar 19 '19 at 20:42

Convert MongoDB field from String to ISODate in array

1 Answers1

Linked