How to export JSON from MongoDB using Robomongo

Question

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!

Solution 1

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.

Solution 2

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 3

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

Solution 4

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

Solution 5

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.

View more solutions

Comments

[Ramesh Murugesan]

You want to export particular collections or full db?

[Stennie]

This isn't a current feature of Robomongo, but I've added a feature suggestion in the github issue queue: Add JSON export. There's a general suggestion that import/export should be integrated, but more detailed/practical use cases would be helpful. For example, should this support exporting JSON from a collection, a find query, an aggregation pipeline? Currently your best option is to use the standard mongoexport command line tool.

[Undefined Variable]

@Stennie - thank you for your comment. I guess in answer to your question - from a customer experience viewpoint, it does not really matter. In most other DB interfaces, the flow is that you run a query (with or without critera), get a set of results. Right click and say "export results as..." So the same should be applicable here. Does not matter if I am exporting an entire collection or a find query. If the result can get displayed in the panel, then it should be exportable.

[Naren]

Simply you can do this mongoexport --uri='mongodb://[email protected]:27017/marketi‌​ng' --collection=contacts --out=contacts.json

[Szczepan Hołyszewski]

@Naren Right click -> "Export as JSON" would be simple. This is not.

[chopin_is_the_best]

Is it possible to write the output of this script into a csv locally whithin Robomongo shell ?

[ruX]

It outputs not valid json. Just json-serialized records one by one

[vinyll]

Studio 3T totally did the job easier than expected! :+1:

[Yuval]

For many use-cases, one can use tojson(db.getCollection(...).find(...)["_batch"]) to output the entirety of the current batch obtained from the server.

[Florian Winter]

@Yuval You mean literally ["_batch"]? Can you give an example how to use this? I tried this with Robo 3T 1.2.1, but it only says "Script executed successfully, but there are no results to show".

[Yuval]

@FlorianWinter That was just from random findings from fiddling around. A better solution is tojson(db.getCollection(...).find(...).toArray()).

[Florian Winter]

@Yuval Nice! That's the easiest solution then, much better than mine. Consider posting it as an answer. (Or edit mine, but then I would get all the credit that you deserve, which would be somewhat unfair...)

[Florian Winter]

@Yuval A downside of your approach may be that the entire collection is loaded into memory, then converted to JSON. Or that it only outputs part of the collection. I did not test it thoroughly. Mine outputs the whole collection and only keeps one document in memory per iteration.

[Yuval]

Your last statement is incorrect: your approach appends each record to the variable records hence all records are kept in memory. There is nothing much to test, it's just more concise code I thought I would share.

[Florian Winter]

@Yuval Thanks for the heads-up. I have improved the script.

[Shanika Ediriweera]

This gives me "Script executed successfully, but there are no results to show"

[Shanika Ediriweera]

This gives me "Script executed successfully, but there are no results to show"

[EugenSunic]

Error: Line 10: Invalid left-hand side in assignment

[Priya Agarwal]

@Florian Winter - Follow up question on your Note : How to convert BinData and ISODate type functions to actual values(the way it is represented in GUI) while export?

[Florian Winter]

@PriyaAgarwal I'm not sure I fully understand your question, but if it is a follow-up question, then I would suggest asking it as a new question. You will be more likely to get help on here if you do so.

[Constantino Cronemberger]

When I have dates in my document it returns ISODate elements which are not valid json format.

[Ilya Luzyanin]

Neat! Not dirty at all considering lack of export functionality in robo3t. Much easier for small sets of data comparing to other suggested solutions.

[maesk]

Why would you use a Word document to store a database export? And why in the world would anyone want to print a database export?

[Charlie Schliesser]

@maesk 🤣🤣🤣🤣

[Visakh Vijayan]

awesome. just what I was looking for

[Shurvir Mori]

Awesome, It helps me lot, Just a little expectation, can we export whole collection in single time. this answer helps to export single single tables.

[realtebo]

Studio 3T is perfect but it has an excesive cost for a solo developer like me. Absurd the cost.

[craastad]

This actually helped solve my problem while my IntelliJ data connection is broken. Sad I have to jump through hoops like this in the year 2021, but hey, it works. Thank you!

[emragins]

This was the best way to get more than 50 records at a time. I just switched the print to print(record) and then was able to copy the json from the text view.

[jcollum]

The dates will come out as ISODate instead of 8601 JSON dates though

[Yuki]

Awesome! Just a reminder - don't run it on large collections or result sets :-)

[santamanno]

This will also include comments between the objects and you need to manually insert a [ at the beginning and a ] at the end. Also, you need to replace ObjectID and ISODate functions.

[cem]

For medium or small results sets, its really great! Thanks.

[Prabhatika Vij]

Just to add to @Yuki's comment, you might get a similar error if you try to run it on large collections: Error: Converting from JavaScript to BSON failed: Object size 52590654 exceeds limit of 16793600 bytes. : shellPrintHelper@src/mongo/shell/utils.js:637:9 @(shell2):1:35.