Mongoimport / MOngoexport utilities

Currently mongoimport and mongoexport supports 3 file formats.

CSV
json
tsv


systax

[mongod@sikki4u1c ~]$  mongoimport -u mongo-root -p passw0rd --db movie --collection score mongoimport.json --authenticationDatabase admin
2019-08-20T11:55:18.907+0000    connected to: localhost
2019-08-20T11:55:18.918+0000    imported 20 documents
[mongod@sikki4u1c ~]$  mongo -u mongo-root -p passw0rd
MongoDB shell version v3.6.13
connecting to: mongodb://127.0.0.1:27017/?gssapiServiceName=mongodb
Implicit session: session { "id" : UUID("68a9cf0d-bfbc-4626-a322-0b24cb9a4811") }
MongoDB server version: 3.6.13
Server has startup warnings:
2019-08-20T05:49:51.204+0000 I CONTROL  [initandlisten]
2019-08-20T05:49:51.204+0000 I CONTROL  [initandlisten] ** WARNING: /sys/kernel/mm/transparent_hugepage/enabled is 'always'.
2019-08-20T05:49:51.204+0000 I CONTROL  [initandlisten] **        We suggest setting it to 'never'
2019-08-20T05:49:51.204+0000 I CONTROL  [initandlisten]
2019-08-20T05:49:51.204+0000 I CONTROL  [initandlisten] ** WARNING: /sys/kernel/mm/transparent_hugepage/defrag is 'always'.
2019-08-20T05:49:51.204+0000 I CONTROL  [initandlisten] **        We suggest setting it to 'never'
2019-08-20T05:49:51.204+0000 I CONTROL  [initandlisten]
> use movie
switched to db movie
> db.score.find()
{ "_id" : 0, "name" : "aimee Zank", "scores" : [ { "score" : 1.463179736705023, "type" : "exam" }, { "score" : 11.78273309957772, "type" : "quiz" }, { "score" : 35.8740349954354, "type" : "homework" } ] }
{ "_id" : 1, "name" : "Aurelia Menendez", "scores" : [ { "score" : 60.06045071030959, "type" : "exam" }, { "score" : 52.79790691903873, "type" : "quiz" }, { "score" : 71.76133439165544, "type" : "homework" } ] }
{ "_id" : 3, "name" : "Bao Ziglar", "scores" : [ { "score" : 71.64343899778332, "type" : "exam" }, { "score" : 24.80221293650313, "type" : "quiz" }, { "score" : 42.26147058804812, "type" : "homework" } ] }
{ "_id" : 2, "name" : "Corliss Zuk", "scores" : [ { "score" : 67.03077096065002, "type" : "exam" }, { "score" : 6.301851677835235, "type" : "quiz" }, { "score" : 66.28344683278382, "type" : "homework" } ] }
{ "_id" : 4, "name" : "Zachary Langlais", "scores" : [ { "score" : 78.68385091304332, "type" : "exam" }, { "score" : 90.2963101368042, "type" : "quiz" }, { "score" : 34.41620148042529, "type" : "homework" } ] }
{ "_id" : 6, "name" : "Jenette Flanders", "scores" : [ { "score" : 37.32285459166097, "type" : "exam" }, { "score" : 28.32634976913737, "type" : "quiz" }, { "score" : 81.57115318686338, "type" : "homework" } ] }

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

if there is any update to the document and you want to append those values to tables

WE need to use --upsert option

[mongod@sikki4u1c ~]$  mongoimport -u mongo-root -p passw0rd --db movie --collection score --upsert mongo_update.json --authenticationDatabase admin

2019-08-20T11:59:10.489+0000    connected to: localhost

2019-08-20T11:59:10.491+0000    imported 2 documents

[mongod@sikki4u1c ~]$

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

To import csv file

mongoimport -u mongo-root -p passw0rd --type csv --db movie --collection score mongo_csv.csv

this may fail because it may happed that the header line has some field name, to exclude that

mongoimport -u mongo-root -p passw0rd --type csv -- headerline --db movie --collection score mongo_csv.csv

If your source file dont have any header line, the you need to specify the column name explicitly

mongoimport -u mongo-root -p passw0rd --type csv -- fields _id,name,scores --db movie --collection score mongo_csv.csv

you can also wite a fieldfile if you have to import csv frequently.

fieldfile should have all the columns

more fieldfiledemo.txt
_id
name
scores


mongoimport -u mongo-root -p passw0rd --type csv -- fieldFile fieldfiledemo.txt --db movie --collection score mongo_csv.csv


++++++++++++++++++++++++++++++++++++++++++
MONGOEXPORT UTILITY
++++++++++++++++++++++++++++++++++++++++++


[mongod@sikki4u1c ~]$  mongoexport -u mongo-root -p passw0rd --db movie --collection score --authenticationDatabase admin >> out.json
2019-08-20T12:14:29.929+0000    connected to: localhost
2019-08-20T12:14:29.930+0000    exported 22 records
[mongod@sikki4u1c ~]$ ls -ltr out.json
-rw-rw-r--. 1 mongod mongod 3843 Aug 20 12:14 out.json
[mongod@sikki4u1c ~]$

other way is to provide --out argument

[mongod@sikki4u1c ~]$  mongoexport -u mongo-root -p passw0rd --db movie --collection score --authenticationDatabase admin --out out_2.json

2019-08-20T12:15:44.853+0000    connected to: localhost

2019-08-20T12:15:44.854+0000    exported 22 records

[mongod@sikki4u1c ~]$ ls -ltr out_2.json

-rw-rw-r--. 1 mongod mongod 3843 Aug 20 12:15 out_2.json

[mongod@sikki4u1c ~]$

If you just want to export only name field

[mongod@sikki4u1c ~]$  mongoexport -u mongo-root -p passw0rd --db movie --collection score --fields name --authenticationDatabase admin --out out_2.json

2019-08-20T12:17:27.360+0000    connected to: localhost

2019-08-20T12:17:27.361+0000    exported 22 records

[mongod@sikki4u1c ~]$ more out_2.json

{"_id":0,"name":"aimee Zank"}

{"_id":1,"name":"Aurelia Menendez"}

{"_id":3,"name":"Bao Ziglar"}

{.

.

.

..

{"_id":18,"name":"Verdell Sowinski"}

{"_id":10,"name":"Denisha Cast"}

{"_id":19,"name":"Gisela Levin"}

{"_id":198,"name":"Timothy Harrod"}

{"_id":199,"name":"Rae Kohout"}

[mongod@sikki4u1c ~]$

exporting to json will always capture _id field  as well. But csv file will not capture

[mongod@sikki4u1c ~]$  mongoexport -u mongo-root -p passw0rd --db movie --collection score --fields name --type csv --authenticationDatabase admin --out out_2.csv

2019-08-20T12:19:09.816+0000    connected to: localhost

2019-08-20T12:19:09.817+0000    exported 22 records

[mongod@sikki4u1c ~]$ more out_2.csv

name

aimee Zank

Aurelia Menendez

Bao Ziglar

Corliss Zuk

Zachary Langlais

Jenette Flanders

Wilburn Spiess

Salena Olmos

Daphne Zheng

Sanda Ryba

Marcus Blohm

Quincy Danaher

Jessika Dagenais

Alix Sherrill

Tambra Mercure

Dodie Staller

Fletcher Mcconnell

Verdell Sowinski

Denisha Cast

Gisela Levin

Timothy Harrod

Rae Kohout

[mongod@sikki4u1c ~]$

NOTE: YOu must supply field names inorder to export to csv, if you dont, mongoexport will fail

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Now i have a PEEPS collections which is little complex. json can represent arbitary obj structure and nested objects. But what if need this data in CSV

> db.peeps.find().pretty()

{

        "_id" : ObjectId("5d52b9237aa191d958d087e3"),

        "name" : "dude",

        "born" : ISODate("1984-04-01T00:00:00Z"),

        "likes" : [

                "naps",

                "cake"

        ],

        "points" : 1

}

>

Need to update this part

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Selective export: This is to extract selected database. say for a query. The resultset of query can be exported

[mongod@sikki4u1c ~]$  mongoexport -u mongo-root -p passw0rd --db movie --collection score --query '{_id:{$gt:10}}' --authenticationDatabase admin
2019-08-20T13:09:18.333+0000    connected to: localhost

{"_id":11,"name":"Marcus Blohm","scores":[{"score":78.42617835651868,"type":"exam"},{"score":82.58372817930675,"type":"quiz"},{"score":87.49924733328717,"type":"homework"}]}

{"_id":12,"name":"Quincy Danaher","scores":[{"score":54.29841278520669,"type":"exam"},{"score":85.61270164694737,"type":"quiz"},{"score":80.40732356118075,"type":"homework"}]}

{"_id":13,"name":"Jessika Dagenais","scores":[{"score":90.47179954427436,"type":"exam"},{"score":90.3001402468489,"type":"quiz"},{"score":95.17753772405909,"type":"homework"}]}

{"_id":14,"name":"Alix Sherrill","scores":[{"score":25.15924151998215,"type":"exam"},{"score":68.64484047692098,"type":"quiz"},{"score":24.68462152686763,"type":"homework"}]}

{"_id":15,"name":"Tambra Mercure","scores":[{"score":69.1565022533158,"type":"exam"},{"score":3.311794422000724,"type":"quiz"},{"score":45.03178973642521,"type":"homework"}]}

{"_id":16,"name":"Dodie Staller","scores":[{"score":7.772386442858281,"type":"exam"},{"score":31.84300235104542,"type":"quiz"},{"score":80.52136407989194,"type":"homework"}]}

{"_id":17,"name":"Fletcher Mcconnell","scores":[{"score":39.41011069729274,"type":"exam"},{"score":81.13270307809924,"type":"quiz"},{"score":97.70116640402922,"type":"homework"}]}

{"_id":18,"name":"Verdell Sowinski","scores":[{"score":62.12870233109035,"type":"exam"},{"score":84.74586220889356,"type":"quiz"},{"score":81.58947824932574,"type":"homework"}]}

{"_id":19,"name":"Gisela Levin","scores":[{"score":44.51211101958831,"type":"exam"},{"score":0.6578497966368002,"type":"quiz"},{"score":93.36341655949683,"type":"homework"}]}

{"_id":198,"name":"Timothy Harrod","scores":[{"score":11.9075674046519,"type":"exam"},{"score":20.51879961777022,"type":"quiz"},{"score":64.85650354990375,"type":"homework"}]}

{"_id":199,"name":"Rae Kohout","scores":[{"score":82.11742562118049,"type":"exam"},{"score":49.61295450928224,"type":"quiz"},{"score":28.86823689842918,"type":"homework"}]}

2019-08-20T13:09:18.335+0000    exported 11 records

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

If there is a requirement that a file should have limited number of lines, then you can break the resultset into multiple files. For this you need to sort the id field so that is in order.

[mongod@sikki4u1c ~]$  mongoexport -u mongo-root -p passw0rd --skip 0 --limit 2 --db movie --collection score  --authenticationDatabase admin

2019-08-20T13:12:46.849+0000    connected to: localhost

{"_id":0,"name":"aimee Zank","scores":[{"score":1.463179736705023,"type":"exam"},{"score":11.78273309957772,"type":"quiz"},{"score":35.8740349954354,"type":"homework"}]}

{"_id":1,"name":"Aurelia Menendez","scores":[{"score":60.06045071030959,"type":"exam"},{"score":52.79790691903873,"type":"quiz"},{"score":71.76133439165544,"type":"homework"}]}

2019-08-20T13:12:46.850+0000    exported 2 records

[mongod@sikki4u1c ~]$  mongoexport -u mongo-root -p passw0rd --skip 2 --limit 2 --db movie --collection score  --authenticationDatabase admin

2019-08-20T13:12:58.994+0000    connected to: localhost

{"_id":3,"name":"Bao Ziglar","scores":[{"score":71.64343899778332,"type":"exam"},{"score":24.80221293650313,"type":"quiz"},{"score":42.26147058804812,"type":"homework"}]}

{"_id":2,"name":"Corliss Zuk","scores":[{"score":67.03077096065002,"type":"exam"},{"score":6.301851677835235,"type":"quiz"},{"score":66.28344683278382,"type":"homework"}]}

2019-08-20T13:12:58.994+0000    exported 2 records

[mongod@sikki4u1c ~]$

And so on.....