How export a specific query in mongo to csv?

Question

I'm am searching for either on how to export a query result from mongo to CVS or excel, or how to export results in robomongo. I found mongoexport but I think that only can export a collection with some simple constraints.

This is my query:

 db.getCollection('user').find({ "coins": { $elemMatch: { "id":"30","amount":0} }  })

Solution 1

For MongoDB 3.0+, you can specify the query into mongoexport using -q and --type options:

mongoexport -d test -c user -q '{ coins: { $elemMatch: { "id":"30","amount":0}}}' --type=csv --out exportdir/myRecords.json

For earlier versions, use --csv option with the header fields:

mongoexport -d test -c user -q '{ coins: { $elemMatch: { "id":"30","amount":0}}}' --csv -f first_name,last_name,title --out exportdir/myRecords.json

Solution 2

You can use forEach to look through each result and a function to format them. Something like:

db.getCollection('user').find({ "coins": { $elemMatch: { "id":"30","amount":0} }  })
.forEach(function(u){

  print('"' + u._id + '","' + u.amount + '"');

});

Then just send the output to a file.

Comments

[jhonny lopez]

can't use mongoexport in this case because data es deep, for example column color have red,blue,yellow .. end others are similars. mongoexport undestand that is for simple rows (one color, not arrays).

[PostureOfLearning]

Ben, how do I "just send the output to a file"?

[Hussain Fakhruddin]

@PostureOfLearning save your script as js file script.js mongo test script.js > out.csv