how do I export a datetime field in string format with mongo export?

Question

I'm trying to export an entire collection from mongo with mongoexport. Most answers I've found around this, regarding dates, involve a query. I'm not doing a query. I'm dumping the entire table, to JSON format. The datetime fields are exporting like so:

"dateOfBirth": {
  "$date": "1999-02-02T00:00:00.000Z"
}

I only want the string representation of the datetime field, so it looks like:

"dateOfBirth": "1999-02-02T00:00:00.000Z"

I don't know our exact version of mongo, but it's pretty recent.

How do I do this?

SunSmiles · Answer 1 · 2019-01-10T16:29:11.357

0

One can have this format by using the dateToString operator. So, in your case JUST for the date part it will be

 "dateOfBirth" :  { $dateToString: { format: "%Y-%m-%d", date: 
 "$dateOfBirth" } }

Check the possibilities of the time format here or here as per your need

edited Jan 10 '19 at 16:29

answered Jan 10 '19 at 16:15

SunSmiles

186
9

score 0 · Answer 2 · answered Nov 04 '21 at 21:00

Here's a Python script that will convert mongo's unfriendly date format to a string format in your exported json dump:


######################################
# About                              #
######################################

# Author:   Eric Arnol-Martin https://eamster.tk
# Purpose:  Replaces Mongo's Exported DateTime Format with Proper DateTime String Representations for Easier Import for Other Databases / Programming Languages
# Expects:  Mongo JSON Exported Pretty File.  
#           For example, a file produced by a command similar to 
#           "mongoexport -d {db_name} --host localhost:27017 -c {table_name} --jsonArray --pretty -o {table_name}.json"
# Tests:    RegEx Test Link:  https://regexr.com/68l7r
# Outputs:  Creates a copy of the input JSON file with DateTime objects replaced with their proper string representation in the same directory as the original file 
#           with the same file name suffixed with "_NEW" at the end of it.
# Sources:  https://stackoverflow.com/questions/2503413/regular-expression-to-stop-at-first-match
#           https://stackoverflow.com/questions/159118/how-do-i-match-any-character-across-multiple-lines-in-a-regular-expression

######################################
# Imports                            #
######################################

import re
from os.path import exists
import fileinput


######################################
# Actual Program                     #
######################################

path = input ("Enter path or name of file to parse: ")
prevPiece = None
content_new = ""
boolReplaceFromNextLine = False
boolHandlingLong = False

if exists(path):
    # Clear new file
    b = open(path + "_NEW", "w+")
    b.close()
    count = 0
    recordCount = 0

    for line in fileinput.input(files=path):
        count = count + 1
        if '"$date":' in line: 
            content_new = content_new[0:content_new.rindex('{')] + line.replace('"$date": {', '').replace('"$date":', '').replace('\n', '').strip();
            boolReplaceFromNextLine = True
        else:
            if boolReplaceFromNextLine:
                if '"$numberLong":' in line:
                    content_new = content_new + line.replace('"$numberLong":', '').replace('\n', '').strip();
                    boolReplaceFromNextLine = True
                    boolHandlingLong = True
                else:
                    if boolHandlingLong:
                        content_new = content_new + line.replace('}', '').strip();
                        boolReplaceFromNextLine = True
                        boolHandlingLong = False
                    else:
                        boolReplaceFromNextLine = False
                        content_new = content_new + line.replace('}', '').strip() + '\n';
            else:
                content_new = content_new + line        
        
        if line == '},\n' and content_new:
            recordCount = recordCount + 1
            b = open(path + "_NEW", "a+")
            b.write(content_new)
            b.close()   
            content_new = ""
            print('Record ' + str(recordCount) + ' processed... adding it to the file...')
            
        prevPiece = line
    
    if content_new:
        b = open(path + "_NEW", "a+")
        b.write(content_new)
        b.close()   
        content_new = ""
        recordCount = recordCount + 1
        print('Record ' + str(recordCount) + ' processed... adding it to the file...')

Adjust as needed. This worked on a 14GB exported json file. We needed this for a conversion from mongo to SQL Server.

how do I export a datetime field in string format with mongo export?

2 Answers2