csv - MongoDB - mongoexport all objects in nested array

Question

I'm using MongoDB version 2.6.x. And I need to export documents from a specific collection.

mongoexport is the tool which serves the need. However, I do not know how to export all the objects under a nested array. Below is the sample document I have.

{
  "_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"}  
  ] 
}

Below is the mongoexport command

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 has below content

field_array.sub_field_1
field_array.sub_field_2

I get data as below in the csv i.e empty fields.

field_array.sub_field_1,field_array.sub_field_2
,

However, if I specify the index value in fields.txt like below

field_array.0.sub_field_1
field_array.0.sub_field_2

then, I get the below data

field_array.sub_field_1,field_array.sub_field_2
sub_val_1,sub_val_1

i.e, only 1 object in the field_array is returned but not all. But, what I need is as below

field_array.sub_field_1,field_array.sub_field_2
sub_val_1,sub_val_1
sub_val_2,sub_val_2

i.e, all objects in the field_array.

Any help?

Answer 1

MongoExport

To export the property whose value is array of object then unwind the array to make single document and store in 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 know more please visit

$unwind

https://docs.mongodb.org/v3.0/reference/operator/aggregation/unwind/

$out

https://docs.mongodb.org/v3.0/reference/operator/aggregation/out/

$project

https://docs.mongodb.org/v3.0/reference/operator/aggregation/project/