CODEWITHSUNDEEP
mongodb
wbm
wbm

Reputation: 155

Export MongoDB Query to a file

I have a MongoDB query which returns a set of single level BSON objects.

db.companies.aggregate([
    { $unwind: {
        path: "$addresses", includeArrayIndex: "arrayIndex"}
    },
    { $project: {
        hrId:1, 
        arrayIndex:1,
        location:'$addresses.location', 
        street:'$addresses.street', 
        city:'$addresses.city', 
        state:'$addresses.state', 
        country:'$addresses.country', 
        postCode:'$addresses.postCode'} 
    }])

I want to export this to a csv file. I have been looking online for a simple solution but to no avail. I am happy to use a gui/tool to do this if required.

Thanks in advance for your help

Upvotes: 2

Views: 4526

Answers (2)

Saleem
Saleem

Reputation: 8978

Logically we can divide problem into two parts.

1) Run aggregation on given collection

2) Export returned results into external file.

MongoDB doesn't directly support exporting of query results however, we can play a trick and make MongoDB do what we want.

Solution:

1) Export aggregation results to another collection.

2) Using mongoexport export results of that collection into csv file.

Given your example, I'd add $out to end of pipeline.

db.companies.aggregate([
    { $unwind: {
        path: "$addresses", includeArrayIndex: "arrayIndex"}
    },
    { $project: {
        hrId:1, 
        arrayIndex:1,
        location:'$addresses.location', 
        street:'$addresses.street', 
        city:'$addresses.city', 
        state:'$addresses.state', 
        country:'$addresses.country', 
        postCode:'$addresses.postCode'} 
    },
    { $out: "tempCompaniesCollection"}])

This query will store our query result into tempCompaniesCollection collection. Now we can run mongoexport as

mongoexport -d "database" -c "tempCompaniesCollection" -f "hrId,arrayIndex,location,street,city,state,country,postCode" --type csv -o csvfile.csv

This will export results to file named csvfile.csv.

Note: mongoexport do require filed names to be provided if export type is a csv. See mongoexport documentation.

Upvotes: 5

Rafael Schettino
Rafael Schettino

Reputation: 109

MongoChef tool (Free version) allow you to export documents to CSV. http://3t.io/mongochef/

Upvotes: 0

Related Questions