MongoDB - mongoexport all objects in nested array
I am using MongoDB version 2.6.x
. And I need to export documents from a specific collection.
mongoexport
is a tool that serves needs. However, I don't know how to export all objects under the nested array. Below is a sample document.
{
"_id": 1,
"field_1": "value1",
"field_2": "value2",
"field_array": [
{"sub_field_1": "sub_val_1", "sub_field_2": "sub_val_2"},
{"sub_field_1": "sub_val_1", "sub_field_2": "sub_val_2"},
{"sub_field_1": "sub_val_1", "sub_field_2": "sub_val_2"}
]
}
Following is the command mongoexport
mongoexport -d db_name -c collection_name -q '{"field_array.sub_field_1": {$gte: "some_value_1", $lt: "some_value_2"}}' -fieldFile fields.txt --csv > data_report.csv
where fields.txt
is below content
field_array.sub_field_1
field_array.sub_field_2
I am getting data as below in blank csv ie fields
field_array.sub_field_1,field_array.sub_field_2
,
However, if I specify the index value in fields.txt
as shown below
field_array.0.sub_field_1
field_array.0.sub_field_2
then i get below data
field_array.sub_field_1,field_array.sub_field_2
sub_val_1,sub_val_1
ie, only 1 object is returned in field_array, but not all. But I need like below
field_array.sub_field_1,field_array.sub_field_2
sub_val_1,sub_val_1
sub_val_2,sub_val_2
ie, all objects in field_array.
Any help?
source to share
MongoExport
To export a property whose value is an object array, then unwind the array to make a separate document and save to a new collection, then export that collection.
For instance
Database: abc collection: xyz
db.xyz.aggregate([
{$unwind: "$field_array"},
{$project: { _id:0,field_id:"$_id",Innerfield: "$field_array", "field_1": 1,"field_2":1}},
{$out: "aggregate_xyz"}
])
MongoExport syntax
mongoexport --host <hostname> --db <Database Name> --collection <collection Name> --csv --fields fieldname1,fieldname2 --out fileName.csv
Example: Export in CSV format
mongoexport --host localhost --db abc --collection aggregate_xyz --csv --fields field_id,field_1,field_2,Innerfield.sub_field_1,Innerfield.sub_field_2 --out important.csv
Example: Export in JSON format
mongoexport --host localhost --db abc --collection aggregate_xyz --fields field_id,field_1,field_2,Innerfield.sub_field_1,Innerfield.sub_field_2 --out important.json
To find out more visit
$ unwinding
https://docs.mongodb.org/v3.0/reference/operator/aggregation/unwind/
$ from
https://docs.mongodb.org/v3.0/reference/operator/aggregation/out/
$ project
https://docs.mongodb.org/v3.0/reference/operator/aggregation/project/
source to share
It looks like mongoexport cannot export all the elements of an array unless you list all of them with index one by one. Of course, this is unrealistic. This way you can split the array and store the data in a temporary collection and then export from that new collection.
db.collection_name.aggregate([ {
$match : {
"field_array.sub_field_1" : {
$gte : "some_value_1",
$lt : "some_value_2"
}
}
}, {
$project : {
_id : 0,
field_array : 1
}
}, {
$unwind : "$field_array"
}, {
$out : "forcsv"
} ]);
mongoexport -d db_name -c forcsv --fieldFile fields.txt --csv > data_report.csv
source to share