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?

You can run an aggregate query on your docs and then put all data present in field_array to a new collection using $out of aggregate. Then its easy for you to export the documents. Check this link. docs.mongodb.org/manual/reference/operator/aggregation/out — mallik
– mallik, Commented Oct 15, 2014 at 10:15

pradeep gowda · Accepted Answer · 2016-01-12 14:15:05Z

17

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/

answered Jan 12, 2016 at 14:15

pradeep gowda

6627 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

micseydel Over a year ago

Is there a particular reason why field_id might be required as being kept?

Masoud Salehi Over a year ago

@micseydel It's not required, but if you want to know to which original document a specific row belongs, you have to rename the _id field so that $unwind stage doesn't error on duplicate _id.

Masoud Salehi Over a year ago

Actually the $unwind stage doesn't error, but saving the result into a collection ($out stage) may cause error if field_array of a document had more that one elements.

Wizard · Accepted Answer · 2014-10-15 12:48:58Z

3

It seems that mongoexport can not export all elements of array, unless you specify all of them with index one by one. Of course this is unrealistic.
So you can split the array and save data into a temporary collection, then export from this 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

answered Oct 15, 2014 at 12:48

Wizard

4,4311 gold badge17 silver badges15 bronze badges

1 Comment

harry-potter Over a year ago

The import of the splitted collection does not work because it's impossible to add documents with the same _id.

Collectives™ on Stack Overflow

MongoDB - mongoexport all objects in nested array

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related