Reshape Array That Is Stored In A Collection And Export To Csv

I have a collection of Facebook Page Likes (titled pagelikes) that is stored in a Mongo database/JSON file. Below is an example of one entry. { '_id' : ObjectId('4725bf8731b8fa

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.