MongoDB Export to JSON/CSV

In this tutorial, i’ll show you how to backup your mongodb data in JSON or CSV and make the MongoDB data ready to be imported into R, I've recently been using the RMongoDB package for this but thought now that i'm running larger data files i'll switch to csv/json formats that are read faster by R.

Backup DB with mongoexport

Dive into  some of the common use options.

$ mongoexport
Export MongoDB data to JSON or CSV files.

options:
 -h [ --host ] arg         mongo host to connect to ( <set name>/s1,s2 for
 -u [ --username ] arg     username
 -p [ --password ] arg     password
 -d [ --db ] arg           database to use
 -c [ --collection ] arg   collection to use (some commands)
 -q [ --query ] arg        query filter, as a JSON string
 -o [ --out ] arg          output file; if not specified, stdout is used

Export all documents (all fields) into the file “refugees.json“ or “refugees.csv”

$ mongoexport -d refugees -c stats -o refugees.json

connected to: 127.0.0.1
exported 1234242 records

OR

$ mongoexport -d refugees -c stats --csv >  refugees.csv

Export all documents with specific fields “createdAt”, “actionTypes” and “sources” only.

$ mongoexport -d refugees -c stats -f "createdAt,actionTypes,sources" -o refugees.json
connected to: 127.0.0.1
exported 123243251 records

Export all documents with a specific query, in this case, only document with dates created “createdAt > 2013-09-01” will be exported.

$mongoexport -d refugees -c stat -f "createdAt,actionTypes,sources" -q '{"createdAt":{"$gte":new Date(1377982800000)}}' --csv >  refugees.csv
connected to: 127.0.0.1
exported 2320903 records

Connect to remote server like 192.168.xxx.xxx, using username and password.

$ mongoexport -h  192.168.xx.xx  -d refugees -c stats -u johndoe  -p pass123 -o refugees.json
connected to: 192.168.xx.xx
exported 10951 records

Review the exported file.

One issue, i came across was that i wasn’t representing my epoch date value correctly, using the mongo shell: I did the steps below.

> new Date(1377982800000)
ISODate("2013-08-31T21:00:00Z")

If you are looking to convert ISODate to epoch, just call date in the Mongodb shell, something like this:

> new Date(2013,08,01)*11377982800000

Then to verify:

> new Date(1377982800000)

ISODate("2013-08-31T21:00:00Z")

Getting a ranged date would be in these lines:

mongoexport --db refugees --collection stats --query '{"createdAt":{$gt:new Date(1360040400000),$lt:new Date(1360990800000)}, "source" : "31u314lpapd"}' --csv >  refugees.csv

How to Import and Export MongoDB Databases

It’s been a few wonderful weeks of working with MongoDB. Just like in MySQL i’ve already come to a point where i need to play around with db dumps and i looked up online on how to perform mysqldump for mongodb?

It’s always exciting to know that a platform got a solution to your problem, as i came around the mongodump. FYI - looking online i came across many different ways to perform the importing/exporting mongodb databases, and there is some excellent documentation on the mongodb.org site about the various tools.

I moved around my data, and the steps were so straightforward so I thought I'd write down the commands that I used so I can refer to them later, just as i'm doing on all other posts.

Part 1: Exporting from MongoDB

To perform this export of my database, i did simply tell mongodump which database (known as  collection in Mongo :) wonder why? )  to export. So my database is called “refugees”. Note that the mongodump command is a command-line utility and is supposed to be tun from the system command prompt and not the mongodb shell.

mongodump --db refugees --collection stats

This dumps the refugees database into the dump directory. Feel free to leave out the --collection part if you want to export the whole database. 

This is what we have in that directory dump:

refugees
├── stats.bson
└── system.indexes.bson

0 directories, 2 files

So that means that the only collection in my refugees database is the stats collection. You will also notice a  .bson file for each collection in the database, in addition i’ve also noticed that  there is a system indexes collection.

Part 2: How to drop a Mongodb Database (command-line).
What about if you already got another db running and wanted to first drop it then you should run this on the mongo Shell. Don't forget i'm using my db called 'refugees'

> use refugees; 
> db.dropDatabase();

Or run this on the command prompt. 

mongo refugees --eval "db.dropDatabase()"

Part 3: How to import  to MongoDB

Next problem was now i got this data dump, how can i import it to my new server, i simply use the mongorestore command, which accepts either a single .bson file representing a collection, or a directory containing multiple files. I also just found this later online - Here's my example:

mongorestore -d refugees /the/path/that/i/too/to/refugees/directory

I’ve  also noticed that one can specify any database name and path to files that they like. I know i’ll need this again at some point and backing these steps up on my blog :) hoping i find someone else to help too.

How to query MongoDB from R?

At my little start-up we use Mongodb for stats and this is awesome for me. I’m quite new to Mongo though when i worked at Google Inc. i used something called Big table that kind of works the same. As you might have noticed i’m highly using R for all my analyses and that’s how i want to keep it.  

I recently came across RMongo, a database access layer to MongoDB in R as an R package. I’m assuming that you already have R and Mongo installed otherwise you might want to go through my previous tutorials on installations of R or Mongo.

To install RMongo:

>install.packages(“RMongo”)

If that does not work, try downloading it from http://cran.r-project.org/web/packages/RMongo/index.html and run:

install.packages("~/Downloads/RMongo_XX.XX.XX.tar.gz", repos=NULL, type="source")

The Data Querying?

Now from within R, this is how to connect to a local MongoDB. For those with remote databases please know that the mongoDbConnect function takes some additional arguments . 

Run the command below to know these arguments and what they mean.

> library("RMongo")

>?mongoDbConnect()

and you will be presented with a number of various arguments to use.

If you are new to Mongo you might want to go through this wonderful tutorial by nettuts here title “Getting started with mongodb”. This tutorial is awesome though got some few typos!

I did put my database into a test db, so this is how i connect to the db called "test"
> the_mongo  <- mongoDbConnect("test")

Also run

>?dbGetQuery()

> the_result <- dbGetQuery(the_mongo, "nettuts", "", 0, 10)

> the_result

It’s also possible to use more complex queries to extract data.

The main query function takes five arguments as shown by the:

>?dbGetQuery()

• database connection
• collection name
• query
• skip - how many objects to skip
• limit - total number of objects to return

For example, extract all fields from the “nettuts” collection where age is greater than or equal to 47:

> some_result <- dbGetQuery(the_mongo, "nettuts", "{'age' : { '$gte' : 47}}")