Redirect output of mongo query to a csv file
Solution 1
I know this question is old but I spend an hour trying to export a complex query to csv and I wanted to share my thoughts. First I couldn't get any of the json to csv converters to work (although this one looked promising). What I ended up doing was manually writing the csv file in my mongo script.
This is a simple version but essentially what I did:
print("name,id,email");
db.User.find().forEach(function(user){
print(user.name+","+user._id.valueOf()+","+user.email);
});
This I just piped the query to stdout
mongo test export.js > out.csv
where test
is the name of the database I use.
Solution 2
Mongo's in-built export is working fine, unless you want to any data manipulation like format date, covert data types etc.
Following command works as charm.
mongoexport -h localhost -d databse -c collection --type=csv
--fields erpNum,orderId,time,status
-q '{"time":{"$gt":1438275600000}, "status":{"$ne" :"Cancelled"}}'
--out report.csv
Solution 3
Extending other answers:
I found @GEverding's answer most flexible. It also works with aggregation:
test_db.js
print("name,email");
db.users.aggregate([
{ $match: {} }
]).forEach(function(user) {
print(user.name+","+user.email);
}
});
Execute the following command to export results:
mongo test_db < ./test_db.js >> ./test_db.csv
Unfortunately, it adds additional text to the CSV file which requires processing the file before we can use it:
MongoDB shell version: 3.2.10
connecting to: test_db
But we can make mongo shell stop spitting out those comments and only print what we have asked for by passing the --quiet
flag
mongo --quiet test_db < ./test_db.js >> ./test_db.csv
Solution 4
Here is what you can try:
print("id,name,startDate")
cursor = db.<collection_name>.find();
while (cursor.hasNext()) {
jsonObject = cursor.next();
print(jsonObject._id.valueOf() + "," + jsonObject.name + ",\"" + jsonObject.stateDate.toUTCString() +"\"")
}
Save that in a file, say "export.js". Run the following command:
mongo <host>/<dbname> -u <username> -p <password> export.js > out.csv
Solution 5
Have a look at this
for outputing from mongo shell to file. There is no support for outputing csv from mongos shell. You would have to write the javascript yourself or use one of the many converters available. Google "convert json to csv" for example.
Related videos on Youtube
Aafreen Sheikh
Master in Computer Science from University of California, San Diego. Interested in software engineering, databases and machine learning. My profile on LinkedIn : https://www.linkedin.com/pub/aafreen-sheikh/51/b90/372
Updated on July 08, 2022Comments
-
Aafreen Sheikh almost 2 years
I am using MongoDB 2.2.2 for 32-bit Windows7 machine. I have a complex aggregation query in a .js file. I need to execute this file on the shell and direct the output to a CSV file. I ensure that the query returns a "flat" json (no nested keys), so it is inherently convertible to a neat csv.
I know aboutload()
andeval()
.eval()
requires me to paste the whole query into the shell and allows onlyprintjson()
inside the script, while I need csv. And, the second way:load()
..It prints the output on the screen, and again in json format.
Is there a way Mongo can do this conversion from json to csv? (I need csv file to prepare charts on the data). I am thinking:
1. Either mongo has a built-in command for this that I can't find right now.
2. Mongo can't do it for me; I can at most send the json output to a file which I then need to convert to csv myself.
3. Mongo can send the json output to a temporary collection, the contents of which can be easilymongoexported
to csv format. But I think only map-reduce queries support output collections. Is that right? I need it for an aggregation query.Thanks for any help :)
-
WiredPrairie over 11 yearsIf this is something you do frequently, you might consider writing a standalone EXE using .NET, python, or you could use NodeJs; each has a native driver that would make it easy to execute your code and produce the output desired.
-
Aafreen Sheikh over 11 yearsI am referring to Zachary's answer on stackoverflow.com/questions/4130849/… and am able to convert from json to csv. But as an alternative, can I output the json to a collection and then do a mongoexport?
-
WiredPrairie over 11 yearsI'd recommend you just build a small harness using Node and the MongoDB driver for NodeJS and then you can execute whatever code you'd like. You'd get the results you want very quickly without needing the shell at all. It would be very maintainable (and debuggable).
-
-
Nelu over 10 yearsHow would I specify what db the User collection is in?
-
GEverding over 10 years@NeluMalancea check out the MongoDB docs they have this information. You can specify the DB by adding
use <database>
to the top of the script -
Steve Hansen Smythe about 9 yearsActually, since the shell helpers such as "use <database>" are not javascript, they're not permitted. See docs.mongodb.org/manual/tutorial/…. Instead, start your script something like this: conn = new Mongo(); db = conn.getDB('your_db_name');
-
iwein almost 9 years@NeluMalancea the mongo command accepts a db url (and user, pass, …)
-
Zoltán over 8 years@NeluMalancea the
test
in the last command is the name of the database, just replace it with the name of your database. -
Jan over 8 yearsThanks a ton! Hint: now it is
--type=csv
instead of--csv
. -
amahrt over 7 yearsI know this is old but this might help other peoples: You can use a host an a database name like so:
mongo your-host.example.com:27017/dbname
where dbname is your database name and 27017 is the port of the mongod. -
Harry Wood over 7 yearsI had a textual field with lots of nasty user-input values with quotes and newlines, so I also defined a string escaping function like this one within my export script. It works, but I can't help feeling this is all getting very heavy. If MongoDB doesn't do this a more built-in way, then maybe saving as JSON and converting that file to CSV afterwards was a better idea.
-
Renato Back about 6 yearsEditing his answer would be better than adding a new one.
-
Raj006 over 5 yearsThe limitation of the mongoexport is that you can't manipulate the fields. The mongo id exports as ObjectId(mongidstring). Being able to export the results from a mongo shell script is better if someone wants to manipulate the data of the fields (for example ObjectId(mongidstring).toString()).
-
Hendy Irawan almost 5 yearscan I do aggregation operations?
-
nurb over 4 yearsThis solution worked. But for Windows I had to make two amendments: I just needed double apostrophe from outside and single apostrophes inside like this -q "{name:'stackoverflow'}" , also for port specifying -p command didn't work, I used --port 27000.
-
JGFMK about 3 yearsThe Windows zip containing mongoexport is flagged by Virustotal as having Trojan-PSW.Agent - so not going to use that command personally.
-
Adam Gerthel about 2 yearsThis works, but I'm getting
2022-05-20T11:19:13.288+0200 I CONTROL [js] machdep.cpu.extfeatures unavailable
as a first line even with--quiet