mongodb mongoexport 聚合导出到 csv 文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16468602/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
mongoexport aggregate export to a csv file
提问by Baconator507
I want to save the result from an aggregation into a csv file.
我想将聚合结果保存到 csv 文件中。
Using the mongo cmd line tool I can do this to get the results I want:
使用 mongo cmd 行工具我可以这样做以获得我想要的结果：
db.compras.aggregate({ $group : { _id : "$data.proponente", total : { $sum : "$price" } }}
How would I translate this into a mongoexport command that saves results into a csv?
我如何将其转换为将结果保存到 csv 的 mongoexport 命令？
采纳答案by Stennie
You can't run aggregate()queries through mongoexport. The mongoexporttool is intended for more basic data export with a query filter rather than full aggregation and data processing. You could easily write a short script using your favourite language driverfor MongoDB, though.
您不能运行合计（）查询通过mongoexport。该mongoexport工具旨在通过查询过滤器导出更基本的数据，而不是完整的聚合和数据处理。不过，您可以使用您最喜欢的 MongoDB语言驱动程序轻松编写一个简短的脚本。
回答by Matt
Slightly simpler option as of 2.6+ is to now add an $outstep to your aggregate to put the results into a collection:
从 2.6+ 开始，稍微简单的选项是现在$out向聚合中添加一个步骤以将结果放入集合中：
db.collection.aggregate( [ { aggregation steps... }, { $out : "results" } ] )
Then just use mongoexportas:
然后只需mongoexport用作：
mongoexport -d database -c results -f field1,field2,etc --csv > results.csv
After that you might want to delete the temporary collection from the database so that it does not keep using unnecessary resources, and also to avoid confusion later, when you have forgotten why this collection exists in your database.
之后，您可能希望从数据库中删除临时集合，这样它就不会继续使用不必要的资源，同时也避免以后在您忘记为什么该集合存在于您的数据库中时出现混淆。
db.results.drop()
回答by Askar
You can export to a CSV file with the following 3 steps:
您可以通过以下 3 个步骤导出到 CSV 文件：
Assign your aggregation results to a variable (reference):
var result = db.compras.aggregate({ $group : { _id : "$data.proponente", total : { $sum : "$price" } }}Insert a value of the variable to a new collection:
db.bar.insert(result.result);In terminal (or command line) export this barcollection to a CSV file:
mongoexport -d yourdbname -c bar -f _id,total --csv > results.csv
将聚合结果分配给变量 ( reference)：
var result = db.compras.aggregate({ $group : { _id : "$data.proponente", total : { $sum : "$price" } }}将变量的值插入到新集合中：
db.bar.insert(result.result);在终端（或命令行）中，将此栏集合导出到 CSV 文件：
mongoexport -d yourdbname -c bar -f _id,total --csv > results.csv
...and you're done :)
......你就完成了:)
回答by What Would Be Cool
If you don't want to store the results in a collection, you could also write directly to a CSV file from JavaScript using the print function. Save the following script to a file like exportCompras.js.
如果您不想将结果存储在集合中，您也可以使用打印函数从 JavaScript 直接写入 CSV 文件。将以下脚本保存到类似 exportCompras.js 的文件中。
let cursor = db.compras.aggregate({ $group :
{ _id : "$data.proponente",
total : { $sum : "$price" }
}
});
if (cursor && cursor.hasNext()) {
//header
print('proponente,total');
cursor.forEach(item => {
print('"' + item._id + '",' + item.total);
// printjson(item); -- if you need JSON
}
}
From the command line, call
从命令行，调用
mongo server/collection exportCompras.js > comprasResults.csv --quiet
mongo 服务器/集合 exportCompras.js > comprasResults.csv --quiet
Updated from @M.Justin'scomment to replace the previous while loop.
从@M.Justin 的评论更新以替换之前的 while 循环。
回答by Ian Newson
Take the following and save it on the mongo server somewhere:
执行以下操作并将其保存在 mongo 服务器上的某处：
// Export to CSV function
DBCommandCursor.prototype.toCsv = function(deliminator, textQualifier)
{
var count = -1;
var headers = [];
var data = {};
deliminator = deliminator == null ? ',' : deliminator;
textQualifier = textQualifier == null ? '\"' : textQualifier;
var cursor = this;
while (cursor.hasNext()) {
var array = new Array(cursor.next());
count++;
for (var index in array[0]) {
if (headers.indexOf(index) == -1) {
headers.push(index);
}
}
for (var i = 0; i < array.length; i++) {
for (var index in array[i]) {
data[count + '_' + index] = array[i][index];
}
}
}
var line = '';
for (var index in headers) {
line += textQualifier + headers[index] + textQualifier + deliminator;
}
line = line.slice(0, -1);
print(line);
for (var i = 0; i < count + 1; i++) {
var line = '';
var cell = '';
for (var j = 0; j < headers.length; j++) {
cell = data[i + '_' + headers[j]];
if (cell == undefined) cell = '';
line += textQualifier + cell + textQualifier + deliminator;
}
line = line.slice(0, -1);
print(line);
}
}
Then you can run the following commands via the shell or a GUI like Robomongo:
然后你可以通过 shell 或像 Robomongo 这样的 GUI 运行以下命令：
load('C:\path\to\your\saved\js\file')
db.compras.aggregate({ $group : { _id : "$data.proponente", total : { $sum : "$price" } }}.toCsv();
回答by HMagdy
By the same logic, in my case, I want the output as JSON data into out.json
按照相同的逻辑，在我的情况下，我希望将输出作为 JSON 数据放入out.json
1- Create file.json local, not on the server
1-在本地创建file.js ，而不是在服务器上
date = new Date("2019-09-01");
query = {x: true, 'created': {$gte: date}};
now = new Date();
userQuery = {'user.email': {$nin: [/\.dev$|@test\.com$|@testing\.com$/]}};
data = db.companies.aggregate([
{$match: query},
{$sort: {x: 1, created: 1}},
{$lookup: {as: 'user', from: 'users', localField: 'creator', foreignField: '_id'}},
{$addFields: {user: {$arrayElemAt: ['$user', 0]}}},
{$match: userQuery},
{$project: {"_id": 1, "name": 1, "user.email": 1, "xyz": 1}}
]).toArray();
print(JSON.stringify(data));
2- Run
2-运行
mongo server/collection file.js > out.json --quiet
3- Delete header and check out.json
3-删除标题并检查out.json
[
{
"_id": "124564789",
"xyz": [
{
"name": "x",
"value": "1"
},
{
"name": "y",
"value": "2"
}
],
"name": "LOL",
"user": {
"email": "[email protected]"
}
},
....
....
}]
回答by Ankit Kumar Rajpoot
Try this for query-
试试这个查询 -
mongoexport -d DataBase_Name -c Collection_Name --type=csv --fields name,phone,email -q '{_bank:true,"bank.ifsc":{$regex:/YESB/i}}' --out report.csv
