Reshape Array That Is Stored In A Collection And Export To Csv
Solution 1:
You can deal with this in a number of ways.
Firstly if you have MongoDB 3.4 available then you could use a "View" in order to represent the collection with the array contents "un-wound". A "View" is basically an aggregation pipeline statement that appears to be a normal collection as far as most actions that would use a collection are concerned.
So presuming your source collection is called "pages"
here, then you would create the "View" with:
db.createView("pageArray", "pages", [{ "$unwind": "$page_likes" }])
Then you can query the collection as normal:
db.pageArray.find()
/* 1 */
{
"_id" : ObjectId("4725bf8731b8faf4c04595bb"),
"user_id" : "0939bf9w9804842f9f817ad100",
"page_likes" : {
"id" : "859302873383",
"name" : "Hotdogs"
}
}
/* 2 */
{
"_id" : ObjectId("4725bf8731b8faf4c04595bb"),
"user_id" : "0939bf9w9804842f9f817ad100",
"page_likes" : {
"id" : "8593683902",
"name" : "Video Games"
}
}
/* 3 */
{
"_id" : ObjectId("4725bf8731b8faf4c04595bb"),
"user_id" : "0939bf9w9804842f9f817ad100",
"page_likes" : {
"id" : "849204859849028",
"name" : "Road Bikes"
}
}
And subsequently issue the mongoexport
as if it were a normal collection:
mongoexport -d test -c pageArray --type=csv --fields user_id,page_likes.id,page_likes.name
2017-07-05T13:14:11.588+1000 connected to: localhost
user_id,page_likes.id,page_likes.name
0939bf9w9804842f9f817ad100,859302873383,Hotdogs
0939bf9w9804842f9f817ad100,8593683902,Video Games
0939bf9w9804842f9f817ad100,849204859849028,Road Bikes
2017-07-05T13:14:11.589+1000 exported 3 records
Of course adding --out
or a standard redirect to actually output to a file.
If your MongoDB is an older version but at least has $out
available ( from MongoDB 2.6 ) then write to another collection:
db.pages.aggregate([
{ "$unwind": "$page_likes" },
{ "$project": { "_id": 0 } },
{ "$out": "pagesArray" }
])
Then you basically run the same mongoexport
as above since it's also a collection that is accessible to do so.
If you really don't want to create either a "View" or "another collection", then you could simply send a short script to the mongo
shell. Albeit in a very hacky way:
mongo --quiet --eval '
print("user_id,page_likes.id,page_likes.name");
db.pages.aggregate([
{ "$unwind": "$page_likes" },
{ "$project": { "_id": 0 } },
]).forEach(p =>print(`${p.user_id},${p.page_likes.id},${p.page_likes.name}`))'
Or even without aggregate()
and $unwind
at all:
mongo --quiet --eval '
print("user_id,page_likes.id,page_likes.name");
db.pages.find({},{ _id: 0 }).forEach(p =>
p.page_likes.forEach(l =>print(`${p.user_id},${l.id},${l.name}`)))'
Which gives you the same output:
user_id,page_likes.id,page_likes.name0939bf9w9804842f9f817ad100,859302873383,Hotdogs
0939bf9w9804842f9f817ad100,8593683902,Video Games
0939bf9w9804842f9f817ad100,849204859849028,Road Bikes
Note also that if you want or "need" a different delimiter than comma ,
here, then either of the two last approaches with the shell is probably the way to go. As this is "scheduled" for addition to mongoexport
and mongoimport
with TOOLS-87, but of course is "yet to be resolved". So if you want different output, then you do it yourself.
Post a Comment for "Reshape Array That Is Stored In A Collection And Export To Csv"