MongoDB/Mongochef: import from JSON changed dates to string

Question

I have a huge JSON file (about 5 GB) with several fields in all rows. this is what one document looks like:

{ 
 "_id" : ObjectId("55f0105346c5110dfc6b8760"), 
 "patient" : "198", 
 "concept" : "Ampicillin", 
 "confidence" : NumberInt(100), 
 "value" : 1.0, 
 "time" : ISODate("2007-06-27T17:24:00.000+0000"), 
 "stay" : "20220"
}

This is data from hospital stays. For some reason, when I imported the JSON file to MongoDB (using mongoimport in linux command line), the 'time' field was saved not as ISO-date, but as a string. I figured - "no problem" and using mongo-chef altered the field to be of "Date" type using the 'Edit Value' option. I then picked the override option for all documents matching criteria.

screencap of mongochef

What happened then is that the entire collection got the exact same value in the "time" column.

So my question is - either - (A) how can I import it correctly, where mongoimport inserts the "time" as ISOdate, OR, (B) how can I correctly change it in mongochef, after the import. This is time sensitive so PLEASE if anyone has any idea - help!

Thanks!

• profesor79: It is a bit odd that mongoimport did't handle this correctly. what you can do is update strings to dataTime using forEach loop

Answer 1

All right. After digging a LOT I found the correct formatting is:

time: {$date:"2013-11-20T23:32:18Z"}

So I used 'sed' to change it throughout the json file and imported it.

I'm leaving this answer for the benefit of anyone who might come across this problem.

• Kees de Kooter: For MongoChef you have to add the millisecond part: 2016年03月07日T20:03:46.157Z
• Manuel Jordan: Is mandatory use "$date", so "" are mandatory. It is imported later how a ISODate("same pattern used") type