How to import Mongodb ObjectId from CSV file using mongoimport?

Question

I am struggling to import Mongodb's ObjectId from a CSV file using mongoimport:

I tried every combination and escape method I could think of but can't import ObjectId correctly from CSV.

First I tried importing exactly what I get exported from MongoDB to CSV. I am using MongoDB 2.2.1.

I just created two collections and referenced one document's _id in another document:

use yourdb
db.createCollection("student")
db.createCollection("class")
db.student.insert({"name":"Peter"})
db.student.find() returns { "_id" : ObjectId("5143af326d44e1ceb372121d"), "name" : "Peter" }
db.class.insert({"student_id": ObjectId("5143af326d44e1ceb372121d"),"name":"II-4"})

Then I used mongoexport command in shell:

mongoexport -h localhost:3002 -d yourdb -c classes --csv -f student_id,name > export.txt

Resulting CSV looks like this:

student_id,name
ObjectID(5143af326d44e1ceb372121d),"II-4"

Then I imported the resulting CSV using:

mongoimport -h localhost:3002 -d yourdb -c class --type csv --file export.txt --headerline

Quering class collection now returns:

db.class.find()
{ "_id" : ObjectId("5143afc66d44e1ceb372121e"), "student_id" :   ObjectId("5143af326d44e1ceb372121d"), "name" : "II-4" }
{ "_id" : ObjectId("5143b44788df173ba096550e"), "student_id" : "ObjectID(5143af326d44e1ceb372121d)", "name" : "II-4" }

As you can notice student_id field in the second document is actually a string and not MongoDB ObjectId.

I am wrong on something or Mongo can't import it's own exported CSV??

Carbonari

What are you expecting to happen and what's happening instead? You're importing back into the same collection in your example which makes it unclear. – Megalopolis

I am trying to import MongoDB's ObjectID from csv and I am getting only strings imported in MongoDB. I have tried importing MongoDB's csv export but that didn't resulted in ObjectIDs. Seems like no one tried to import ObjectID field from a CSV file using mongoimport? – Carbonari

It worked fine when I tried this. Does your actual test.csv file have a header line containing field names? Your example CSV doesn't show it. – Megalopolis

So you have successfully imported ObjectID - that's great! Yes - my CSV file contains headers. Did you imported CSV exported by MongoDB? I will update my question with exact example. – Carbonari

I could have sworn it worked before, but now that I try it again I'm seeing the same as you where the ObjectID becomes a string. – Megalopolis

Thanks @JohnyHK - I now added the steps to reproduce the issue. – Carbonari

Answer 1

The problem can be reproduced in MongoDB 2.4.1.

The documentation (http://docs.mongodb.org/manual/reference/mongoimport/) states (emphasis by me):

Note Do not use mongoimport and mongoexport for full instance, production backups because they will not reliably capture data type information. Use mongodump and mongorestore as described in “Backup Strategies for MongoDB Systems” for this kind of functionality.

In this discussion https://groups.google.com/forum/?fromgroups=#!topic/mongodb-user/RcnumM5QyxM a similar question was answered as this:

Mongoimport with tsv or csv can only import strings and numbers, and not any of the other types specified in [1]. If you do want to import those types, and if you can produce JSON instead of TSV for your import file, that would be a good way to go; otherwise, you can write a post-processing step which converts the strings to the appropriate MongoDB types (based on some knowledge of what the type of value for a given field should be).

• Dan

[1] http://www.mongodb.org/display/DOCS/Mongo+Extended+JSON

Derrick

So it's not possible in the end they way I thought it was. Thanks for the explanation Ronasta. – Carbonari

Instructions on how to convert your CSV file to compatible JSON in order to preserve types: https://mcmap.net/q/665008/-rails-mongoid-parent-object-not-recognising-child-of-has_many-belongs_to-relation-after-mongoimport – Dichasium

Answer 2

For anyone with this issue who's trying to insert ObjectIds from JSON - it very much IS possible with a bit of modification to the existing data.

Replace:

{ "_id" : ObjectId("5143afc66d44e1ceb372121e"),
  "student_id" : ObjectId("5143af326d44e1ceb372121d"),
  "name" : "II-4" }

With:

{ "_id" : {"$oid":"5143afc66d44e1ceb372121e"},
  "student_id" : {"$oid":"5143af326d44e1ceb372121d"},
  "name" : "II-4" }

Just use a regular expression to replace the ObjectId wrap.

Dedie

Best answer! Should be green! – Tenpins

Yes, should be green! – Stemson