Practical Methods for Exporting MongoDB Query Results to CSV Files

Introduction

In data analysis and visualization tasks, it is often necessary to export query results from MongoDB to CSV format files for further processing with charting tools. However, MongoDB does not natively support converting complex query results to CSV, posing challenges for developers. Based on actual Q&A data, this article delves into achieving this goal through custom scripts and existing tools.

Problem Background and Core Challenges

A user using MongoDB 2.2.2 faced a common issue: how to execute a JavaScript file containing a complex aggregation query and redirect the output directly to a CSV file. The user explicitly noted that the query returns "flat" JSON structures (i.e., no nested keys), which should theoretically convert easily to CSV. The user tried load() and eval() methods but found they only output JSON format or support only printjson() function, failing to meet CSV export needs. Three possible solutions were proposed: finding a built-in MongoDB command, manually converting JSON to CSV, or sending output to a temporary collection for export via mongoexport. However, aggregation queries do not support output to collections, limiting the third approach.

Primary Solution: Custom CSV Generation Script

Based on the best answer (score 10.0), the most effective method is to manually write CSV file content. The core idea is to generate CSV-formatted strings directly in a MongoDB JavaScript script, then redirect output to a file via command line. Here is a detailed implementation process:

First, ensure the query returns flat JSON data, avoiding nested objects or arrays, as CSV format typically does not support complex nesting. For example, if the query involves aggregation, use the $project stage to flatten data.

Next, in the JavaScript script, use the print() function to output CSV headers and data rows. For instance, assuming a User collection with name, _id, and email fields, the script can be written as:

print("name,id,email");
db.User.find().forEach(function(user) {
  print(user.name + "," + user._id.valueOf() + "," + user.email);
});

In this example, the first line outputs CSV headers, and subsequent lines iterate through query results, printing each document's field values separated by commas. Note that the _id field is a special MongoDB type and may require conversion to string using valueOf().

Then, execute the script via command line and redirect output to a CSV file. For example:

mongo test export.js > out.csv

Here, test is the database name, export.js is the file containing the script, and out.csv is the output CSV file. This method is straightforward and suitable for most scenarios, especially when query logic is complex or custom formatting is needed.

Supplementary Method: Using the mongoexport Tool

As a supplementary reference (score 6.5), mongoexport is a built-in MongoDB export tool that can directly export collection data to CSV format. For example:

mongoexport -h localhost -d database -c collection --type=csv --fields erpNum,orderId,time,status -q '{"time":{"$gt":1438275600000}, "status":{"$ne":"Cancelled"}}' --out report.csv

This command exports data from a specified collection, including only erpNum, orderId, time, and status fields, with a query filter applied. However, mongoexport is primarily designed for exporting entire collections or simple queries, with limited support for complex aggregation queries as it cannot directly execute aggregation pipelines from JavaScript files.

Method Comparison and Applicable Scenarios

The custom script method's main advantage is high flexibility, handling arbitrarily complex queries and formatting needs, but it requires manual CSV generation logic. In contrast, the mongoexport tool is easy to use, suitable for exporting entire collections or data based on simple queries, but may be insufficient for aggregation queries. In practice, if queries involve multiple stages or complex transformations, custom scripts are recommended; for simple data exports, mongoexport may be quicker.

Code Examples and In-Depth Analysis

To illustrate the custom script method more clearly, we extend an example. Suppose a sales data collection requires an aggregation query to calculate total sales per product and export to CSV. First, define the aggregation query in a JavaScript file:

var pipeline = [
  { $match: { status: "Completed" } },
  { $group: { _id: "$productId", totalSales: { $sum: "$amount" } } },
  { $project: { productId: "$_id", totalSales: 1, _id: 0 } }
];
var results = db.sales.aggregate(pipeline);
print("productId,totalSales");
results.forEach(function(doc) {
  print(doc.productId + "," + doc.totalSales);
});

This script first matches documents with status "Completed", groups by productId to sum sales, and projects flattened data. Execute via command line: mongo mydb sales_export.js > sales_report.csv to generate the CSV file.

Potential Issues and Optimization Suggestions

When using custom scripts, several issues should be noted: commas or quotes in data may disrupt CSV format, so it is advisable to wrap field values in quotes or escape special characters; large datasets may cause memory issues, so consider using cursors for batch processing. For example, modify the script as:

var cursor = db.User.find().batchSize(100);
print("\"name\",\"id\",\"email\"");
while (cursor.hasNext()) {
  var user = cursor.next();
  print("\"" + user.name + "\",\"" + user._id.valueOf() + "\",\"" + user.email + "\"");
}

Here, each field value is wrapped in quotes, and batch size is set for performance improvement.

Conclusion

In summary, exporting MongoDB query results to CSV files can be achieved through custom scripts, a flexible and powerful method especially suited for complex aggregation queries. While the mongoexport tool offers an alternative, its limitations in complex scenarios make custom scripts a more general solution. With detailed steps and code examples in this article, developers can easily address data export needs, laying a foundation for subsequent data analysis and visualization work.