CODEWITHSUNDEEP
mongodbmongodb-querymongoexport
Karthik
Karthik

Reputation: 2399

extract date from object id and export it to csv in mongodb

I am pretty new to mongodb. Am trying to export data from a collection to a csv file. I have done that and it works fine. I have a question . Is there a way to export just date from ObjectId to a new column. I understand we can get date from ObjectId using ObjectId.getTimestamp(). Is there a way we can do the same for mongoexport. Below is the query i use to export data

mongoexport --db MyDB --collection CollectionName --type=csv --fieldFile fieldsList.txt --out Data.csv

Upvotes: 0

Views: 2020

Answers (1)

Neil Lunn
Neil Lunn

Reputation: 151112

You cannot do this with mongoexport, but if the case is generally simple enough then you can really just use the mongo shell.

For instance to just export data from all fields in a collection with a flat structure and append the last field as the timestamp then you can do:

mongo localhost/MyDB --quiet --eval 'db.CollectioName.find().forEach(d => print(Object.keys(d).concat(["@time"]).map(k => (k === "@time") ? d["_id"].getTimestamp().valueOf() : d[k].valueOf() ).join(", ")))' > Data.csv

Showing the script part as pretty:

db.CollectioName.find().forEach(d =>
  print(Object.keys(d).concat(["@time"]).map(k => 
    (k === "@time") ? d["_id"].getTimestamp().valueOf() : d[k].valueOf() ).join(", ")
  )
)

Which essentially says that when iterating all documents for the given collection we

  • Grab a list of all document fields
  • Append the "special" field of @time to the end of the list
  • Loop those fields and return an array of values - where the @time gets the timestamp from the ObjectId in _id
  • Join the result array with commas and print all of it out

If you had a list of fields then you could simply replace the Object.keys(d) part with an array of field names, such as:

db.CollectioName.find().forEach(d =>
  print(["_id","field1","field2"].concat(["@time"]).map(k => 
    (k === "@time") ? d["_id"].getTimestamp().valueOf() : d[k].valueOf() ).join(", ")
  )
)

But really as long as you provide the database to connect to and the --quiet and --eval options with the script line, then you can simply redirect the output to your destination file, from any scripting you want.

It does not take all considerations for a CSV into account. But it is a "quick and dirty" solution for most basic cases at a pinch, or at the very least a starting point for expansion without writing a full program listing.

If you really want more than this, then there are drivers for your language of choice as well as a plethora of CSV writing libraries for every single one of those languages. And it's really not that much harder than the listing here, especially with a library taking all "quoting" considerations into mind.

Upvotes: 2

Related Questions