How do I mongoexport attributes from an array of objects to CSV?

Question

I need to export values of objects in an array to CSV. Let's say my document is:

{
    name:"test",
    types:[
        {type:"A"},
        {type:"B"},
                {type:"C"}
    ]
}

My goal is to produce output like:

"test", "A"
"test", "B"
"test", "C"

The following would also be acceptable:

"test", "A,B,C"

I'm trying to accomplish this via mongoexport using:

mongoexport -h localhost -d mydb -c mycollection -f name,types.type --csv

Unfortunately, I'm getting:

"test",

I've found documentation on referencing specific array elements, like "types.0.type", but the length of my array is unknown. Any ideas?

I have the exact same scenario. I would rather not write a custom script to handle this, because I want the primary responsibility to be on our data analysts (who can run mongoexport) rather than my development team. Were you able to find a workaround for this? — feathj
– feathj, Commented Jun 27, 2012 at 14:11

arober11 · Accepted Answer · 2012-11-15 12:58:18Z

If your happy with Perl then the ARJsonLib.pm library in the following article, provides the majority of functionality you'll need, to create your own little toy. Note the version in the article is a stub from a toy I hacked together that does exactly what you want along with some other stuff, but as not a mongoDB article it's lacking one function you'll need, that finds the fields/keys in a mongoDB collection, and stores them in an array, but trivial to reconstruct, just write yourself something that pull's n documents from your collection, pushes them into an array and calls findKeysInJsonColl(). Anyway a couple of the functions will take a MongoDB cursor as parameter, and:

convertToDojoGrid()
convertToExcel()

Again the CSV output is missing, but trivial to add back to convertToExcel().

e.g.

...
my $iRows  = convertToExcel("/tmp/test.xlsx", $oMongoData, "", \@aOutFields, "xlsx");
...

Where: $oMongoData is a MongoDB Cursor reference and @aOutFields an array containing the fields/keys you wish to appear in the sheet,

sralmai · Accepted Answer · 2013-04-01 21:09:22Z

1

You'll have to write a custom script that iterates through collections and exports documents in desired format. Built-in mongoexport was not designed for use cases like yours.

edited Apr 1, 2013 at 21:09

sralmai

1971 silver badge5 bronze badges

answered May 6, 2012 at 3:39

Sergio Tulentsev

231k43 gold badges381 silver badges373 bronze badges

Comments

nomDePlum · Accepted Answer · 2015-02-18 00:18:32Z

You can accomplish what your are trying to do by using the MongoDb's aggregation pipeline operations to build out a temporary collection with the data in the shape you want to export:

use mydb
db.mycollection.aggregate([ 
                          {$unwind: "$types"}, 
                          {$project: { _id: 0, name: 1, type: "$types.type" } }, 
                          {$out: "tmp4csv"} 
                          ]);

this will create a tmp4csv collection which contains a document for each element of the array

i.e in your example (and adding a _id value as this value has to be taken into consideration in my suggested solution)

{   
    _id: ObjectId("54e3ce75cb87e6d036287cc6"),
    name:"test",
    types:[
        {type:"A"},
        {type:"B"},
        {type:"C"}
        ]
}

through the use of the $unwind operator becomes

{ _id: ObjectId("54e3ce75cb87e6d036287cc6"), name:"test", types:[ {type:"A"} ] },
{ _id: ObjectId("54e3ce75cb87e6d036287cc6"), name:"test", types:[ {type:"B"} ] },
{ _id: ObjectId("54e3ce75cb87e6d036287cc6"), name:"test", types:[ {type:"C"} ] }

Next the $project operator is used to reform the data slightly - unpacking types.type into type and providing a new unique _id value for each document.

Finally the documents generated by the aggregation pipeline are output in to the "tmp4csv" collection using the $out operator. Without the creation of a unique _id value in the previous step this step would fail due to duplicate keys.

You can then export the data to an output file:

mongoexport -h localhost -d mydb -c tmp4csv -f name,type --csv --out output.csv

And to keep everything clean I would then drop the tmp4csv collection that was created as part of this:

use mydb
db.tmp4csv.drop()

This should give you a csv file with your preferred output format.

References: Aggregation pipeline doc: http://docs.mongodb.org/manual/aggregation/ Unwind operator as this is key: http://docs.mongodb.org/manual/reference/operator/aggregation/unwind/

Collectives™ on Stack Overflow

How do I mongoexport attributes from an array of objects to CSV?

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related