[mongodb] How to export JSON from MongoDB using Robomongo

So I do not know much about MongoDB. I have RoboMongo using which I connect to a MongoDB. What I need to do is this - there is a collection in that MongoDB. I want to export the data from that collection so that I can save it into a file.

I used the interface to open the data from the collection as text and did a Ctrl + A and pasted into a text file. However, I found that not all data is copied and also that there were many comments in the text data which naturally breaks the JSON.

I am wondering if RoboMongo has a Export As JSON facility so that I can do a clean export.

Any pointers are appreciated!

This question is related to mongodb robo3t

The answer is


An extension to Florian Winter answer for people looking to generate ready to execute query.

drop and insertMany query using cursor:

{
    // collection name
    var collection_name = 'foo';

    // query
    var cursor = db.getCollection(collection_name).find({});

    // drop collection and insert script
    print('db.' + collection_name + '.drop();');
    print('db.' + collection_name + '.insertMany([');

    // print documents
    while(cursor.hasNext()) {
        print(tojson(cursor.next()));

        if (cursor.hasNext()) // add trailing "," if not last item
            print(',');
    }

    // end script
    print(']);');
}

Its output will be like:

db.foo.drop();
db.foo.insertMany([
{
    "_id" : ObjectId("abc"),
    "name" : "foo"
}
,
{
    "_id" : ObjectId("xyz"),
    "name" : "bar"
}
]);

  1. make your search
  2. push button view results in JSON mode
  3. copy te result to word
  4. print the result from word

I don't think robomongo have such a feature. So you better use mongodb function as mongoexport for a specific Collection.

http://docs.mongodb.org/manual/reference/program/mongoexport/#export-in-json-format

But if you are looking for a backup solution is better to use

mongodump / mongorestore

If you want to use mongoimport, you'll want to export this way:

db.getCollection('tables')
  .find({_id: 'q3hrnnoKu2mnCL7kE'})
  .forEach(function(x){printjsononeline(x)});

Expanding on Anish's answer, I wanted something I can apply to any query to automatically output all fields vs. having to define them within the print statement. It can probably be simplified but this was something quick & dirty that works great:

var cursor = db.getCollection('foo').find({}, {bar: 1, baz: 1, created_at: 1, updated_at: 1}).sort({created_at: -1, updated_at: -1});

while (cursor.hasNext()) {
    var record = cursor.next();
    var output = "";
    for (var i in record) {
      output += record[i] + ",";
    };
    output = output.substring(0, output.length - 1);
    print(output);
}

There are a few MongoDB GUIs out there, some of them have built-in support for data exporting. You'll find a comprehensive list of MongoDB GUIs at http://mongodb-tools.com

You've asked about exporting the results of your query, and not about exporting entire collections. Give 3T MongoChef MongoDB GUI a try, this tool has support for your specific use case.


You can use tojson to convert each record to JSON in a MongoDB shell script.

Run this script in RoboMongo:

var cursor = db.getCollection('foo').find({}, {});
while(cursor.hasNext()) {
    print(tojson(cursor.next()))
}

This prints all results as a JSON-like array.

The result is not really JSON! Some types, such as dates and object IDs, are printed as JavaScript function calls, e.g., ISODate("2016-03-03T12:15:49.996Z").

Might not be very efficient for large result sets, but you can limit the query. Alternatively, you can use mongoexport.


Solution:

mongoexport --db test --collection traffic --out traffic.json<br><br>

enter image description here

Where:
database -> mock-server
collection name -> api_defs
output file name -> childChoreRequest.json


Don't run this command on shell, enter this script at a command prompt with your database name, collection name, and file name, all replacing the placeholders..

mongoexport --db (Database name) --collection (Collection Name) --out (File name).json

It works for me.


Robomongo's shell functionality will solve the problem. In my case I needed couple of columns as CSV format.

var cursor = db.getCollection('Member_details').find({Category: 'CUST'},{CustomerId :1,Name :1,_id:0})

while (cursor.hasNext()) {
    var record = cursor.next();   
    print(record.CustomerID + "," + record.Name)
}

Output : -------

334, Harison
433, Rechard
453, Michel
533, Pal

A Quick and dirty way: Just write your query as db.getCollection('collection').find({}).toArray() and right click Copy JSON. Paste the data in the editor of your choice.

enter image description here


I had this same issue, and running script in robomongo (Robo 3T 1.1.1) also doesn't allow to copy values and there was no export option either. The best way I could achieve this is to use mongoexport, if mongodb is installed on your local, you can use mongoexport to connect to database on any server and extract data

To connect to Data on remote server, and csv output file, run the following mongoexport in your command line

mongoexport --host HOSTNAME --port PORT --username USERNAME --password "PASSWORD" --collection COLLECTION_NAME --db DATABASE_NAME --out OUTPUTFILE.csv --type=csv --fieldFile fields.txt

fieldFile: helps to extract the desired columns, ex: contents of fields.txt can be just:

userId

to only extract values of the column 'userId'

Data on remote server, json output file:

mongoexport --host HOST_NAME --port PORT --username USERNAME --password "PASSWORD" --collection COLECTION_NAME --db DATABASE_NAME --out OUTPUT.json

this extracts all fields into the json file

data on localhost (mongodb should be running on localhost)

mongoexport --db DATABASE_NAME --collection COLLECTION --out OUTPUT.json

Reference: https://docs.mongodb.com/manual/reference/program/mongoexport/#use


Using a robomongo shell script:

//on the same db
var cursor = db.collectionname.find();

while (cursor.hasNext()) {
    var record = cursor.next();   
    db.new_collectionname.save(record);
}

Using mongodb's export and import command

You can add the --jsonArray parameter / flag to your mongoexport command, this exports the result as single json array.

Then just specify the --jsonArray flag again when importing.

Or remove the starting and ending array brackets [] in the file, then your modified & exported file will import with the mongoimport command without the --jsonArray flag.

More on Export here: https://docs.mongodb.org/manual/reference/program/mongoexport/#cmdoption--jsonArray

Import here: https://docs.mongodb.org/manual/reference/program/mongoimport/#cmdoption--jsonArray


you say "export to file" as in a spreadsheet? like to a .csv?

IMO this is the EASIEST way to do this in Robo 3T (formerly robomongo):

  1. In the top right of the Robo 3T GUI there is a "View Results in text mode" button, click it and copy everything

  2. paste everything into this website: https://json-csv.com/

  3. click the download button and now you have it in a spreadsheet.

hope this helps someone, as I wish Robo 3T had export capabilities