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?
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,
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.
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/
