Append to mongodb from csv

Question

I have a mongodb with collection having fields email. fName, lName. And I have a csv with :

email | contactNo | countryName
a1    | 001       | USA        
a2    | 002       | UAE

I want to merge csv Data with mongoDB data with email as primary key. The final mongo db should have fields like:

{
email:a1,
fName: tom,
lname: cruise,
contactNo: 001,
county : USA
}

Can I do this using mongoimport? Or how can I write a node/python script for same. Any link/refrence will be helpful.

David Makogon David Makogon · Accepted Answer · 2018-03-07T14:58:45

Starting with v3.4, mongoimport supports the --merge flag. By default, it matches on _id, upserting any matches and inserting new id's. You can use the --upsertFields flag to specify an alternate field to match on (such as email in your case).

So, as an example: here is a simple collection, based on the data you have, which I created (I edited the formatting to make it fit better here):

db.contacts.find()
{
    "_id" : ObjectId("5aa019b79259d12040e7107b"),
    "fname" : "tom", "lname" : "cruise", "email" : "a1"
}
{
    "_id" : ObjectId("5aa019bb9259d12040e7107c"),
    "fname" : "bob", "lname" : "smith", "email" : "a2"
}
{
    "_id" : ObjectId("5aa019be9259d12040e7107d"),
    "fname" : "jane", "lname" : "jones", "email" : "a3"
}

And here is my csv file, for import:

email,contactNo,countryName
a1,1,USA
a2,2,UAE
a5,5,CAN

I now import with the following, specifying the email property as the property to key on (not _id):

mongoimport --mode merge --headerline --upsertFields email
 --db stackoverflow --collection contacts --file ~/import.csv --type csv

Once done, my data now looks like this:

db.contacts.find().pretty()
{
    "_id" : ObjectId("5aa019b79259d12040e7107b"),
    "fname" : "tom", "lname" : "cruise", "email" : "a1",
    "contactNo" : 1, "countryName" : "USA"
}
{
    "_id" : ObjectId("5aa019bb9259d12040e7107c"),
    "fname" : "bob", "lname" : "smith", "email" : "a2",
    "contactNo" : 2, "countryName" : "UAE"
}
{
    "_id" : ObjectId("5aa019be9259d12040e7107d"),
    "fname" : "jane", "lname" : "jones", "email" : "a3"
}
{
    "_id" : ObjectId("5aa019d17cd320319146756d"),
    "email" : "a5",
    "contactNo" : 5, "countryName" : "CAN"
}

Notice that:

the first two documents have augmented to include data from the csv
the third document is untouched, as there was no matching data in the csv
the fourth document is brand-new, only containing properties from the csv

Also note that the _id property had nothing do do with the merge, because I specified the --upsertFields option when importing.

Read here for more information.

Append to mongodb from csv

1 Answers