Importing data via mongoimport and dates

[Guy_Nicholson]

Hey, wonder if somebody can help, I have imported data in a JSON file that holds many documents for my collection, its like a bulk load. The file was generated from querying a relational DB and constructing the JSON. After much trail and error I have it working and I can load data and via the documents in Atlas (compass will not show them as it states its invalid UT8, but thats a different issue) My issue is the dates i have imported are a string, and I need them to be a date, so I can query on them effectively without converting (thus making use of index) does anybody know how i load this data with dates as dates. Thanks in hope

[Prasad_Saya]

Hello @Guy_Nicholson, welcome to the MongoDB Community forum!

There is a feature in mongoimport which allows importing dates from other formats into MongoDB Date type. But, this is possible only when importing CSV data (not JSON, as in your case). An example usage in the documentation, below:

Import CSV with Specified Field Types

[Guy_Nicholson]

Thanks for the reply Prasad, yes i came across that in the documentation, doesnt help me in this instance as, as you say its a JSON. I think the answer may lay in extended JSON, I am trying to output (create) my JSON source data with extended format, I “Think” if i use $Date in the definition that may work, I will try it and come back. Many thanks

[Prasad_Saya]

@Guy_Nicholson, yes mongoimport converts the Extended JSON date value imported from JSON to MongoDB Date field type.

[Tim_Fogarty]

Hi @Guy_Nicholson, mongoimport parses JSON files as Extended JSON. This is a format which can be used to express type information. You can read about how to express a date in Extended JSON here:

MongoDB Extended JSON (v2) — MongoDB Manual

You will need to edit your JSON document so that the date field is correctly formatted. You could do this with a number of tools, e.g. sed, jq, a text editor that can find/replace with regexes, or others.

Then you will be able to use mongoimport to load the data as dates, not strings.

[Prasad_Saya]

@Guy_Nicholson

I “Think” if i use $Date in the definition that may work, I will try it and come back

There is an option that after importing the data into MongoDB collection, with the date as string data type, you can convert the data type to date. This requires an additional update operation on the collection.

For example, consider a document with string date field:

{ createdDate: "2021-07-28" }

The update operation:

db.collection.updateMany(
  { },
  [
    {        
      $set: { createdDate: { $dateFromString: { dateString: "$createdDate" } } }
    }
  ]
)

The updated document with the transformed field:

{ "createdDate" : ISODate("2021-07-28T00:00:00Z") }

Note that there $dateFromString operator can take date strings of various formats: $dateFromString

[Guy_Nicholson]

Thanks Prasad, that’s really good to know, I didn’t think of that so that’s a good bit of info for next time. As a FYI. I exported the data in extended JSON thus used $date, this worked, but if I don’t have that option next time or in the future the update would do it nicely, so thank you very much for that.