Hello!
I’m exporting my collections data to a cloud storage for later querying (on Dremio). For that, I’m using mongoexport, as I need the data in JSON format.
The problem is on my datetime fields. They are ISODates on database and were exported on this format: 2022-09-04T23:00:53.215Z. That would be very nice if all dates were exported this way… but when a datetime has 0 milisseconds (like 2022-09-04T23:00:53.000Z on db), I got this on exported JSON: 2022-09-04T23:00:53Z (without the 000 at the end). It gaves me format inconsistency when reading on Dremio.
Other tools as Studio3T export the same data on another pattern, like 2022-09-04T23:00:53.000+0000, and that works very fine… but I couldn’t mimic this kind of export in bash.
How could I change this datetime export behavior (maybe forcing al the fields having the milisseconds part)?
P.S.: I could convert my date fields to string, but then I’ll lose my date index.
After a lot of research, I ended up solving the problem. I have seen that there have been several changes to the mongoexport date format standard since the first versions of Mongo.
The version of mongoexport that comes with MongoDB 4.0.27 exports dates in the correct format, keeping the 000 Z if the date is not milliseconds long and maintaining a consistent pattern throughout the file. In version 4.2.21 mongoexport, the zeros are removed, where instead of 2022-09-04T23:00:53.000Z, the date appears as 2022-09-04T23:00:53Z.
Obs.: now Dremio reads the files without problem. Even gzipped.
Happy to know you found a solution to this, to add a bit more from my side, as per this documentation of MongoDB Extended JSON (v2) - The date/time has a maximum time precision of milliseconds:
Fractional seconds have exactly 3 decimal places if the fractional part is non-zero.
Otherwise, fractional seconds SHOULD be omitted if zero.
If this is an issue for your application, one can try one of below:
By using canonical json output by adding --jsonFormat=canonical in your mongoexport query. By default this is relaxed.
