How to convert MongoDb query into csv (excel format)

Question

This is my query. I would like to export the output of this query into csv (excel format) so i can have a table of data. How can i do this? I am using Robo3t.

(db.getCollection('sentimentOpinions').aggregate([ 
  { $match : { objectType : "Security" } },

  { $lookup:{
             from: "securities",       
       localField: "objectId",  
     foreignField: "id",
               as: "StockID" }},

  { $unwind:"$StockID" },

  { $lookup:{
             from: "users", 
       localField: "userId", 
     foreignField: "userId",
               as: "USER_ARJ" }},

  { $unwind:"$USER_ARJ" },

  {$project :{USER_ID : "$userId",
                NAME : { $concat: [ "$USER_ARJ.profile.firstName",", ", 
                                 "$USER_ARJ.profile.lastName" ] }, 
          SECURITY_ID: "$StockID.id", 
             SECURITY: "$StockID.displayName", 
               TICKER: "$StockID.symbols.yahoo",
        OPINION_VALUE: "$value",
         OPINION_DATE: "$opinionDate"}}, 

         { $sort : { OPINION_DATE : -1 } } ]))

score 2 · Accepted Answer · answered Aug 23 '18 at 12:18

First, you need to make the proper format your data using this query, and output is saved a new collection as new_col by using $out

        (db.getCollection('sentimentOpinions').aggregate([ 
      { $match : { objectType : "Security" } },

      { $lookup:{
                 from: "securities",       
           localField: "objectId",  
         foreignField: "id",
                   as: "StockID" }},

      { $unwind:"$StockID" },

      { $lookup:{
                 from: "users", 
           localField: "userId", 
         foreignField: "userId",
                   as: "USER_ARJ" }},

      { $unwind:"$USER_ARJ" },

      {$project :{USER_ID : "$userId",
                    NAME : { $concat: [ "$USER_ARJ.profile.firstName",", ", 
                                     "$USER_ARJ.profile.lastName" ] }, 
              SECURITY_ID: "$StockID.id", 
                 SECURITY: "$StockID.displayName", 
                   TICKER: "$StockID.symbols.yahoo",
            OPINION_VALUE: "$value",
             OPINION_DATE: "$opinionDate"}}, 

             { $sort : { OPINION_DATE : -1 } },
           {"$out" : "new_col"}
 ]))

Now you export your new collection what format you want by using mongoexport

mongoexport --db db_name --collection new_col_name(new_col) --type csv --fields USER_ID,NAME,SECURITY_ID,SECURITY,TICKER,OPINION_VALUE,OPINION_DATE --out out_file.csv

What if you don't want to create a new collection and write the queried data on the go in csv file? because every now and then it will create new collection and this wont be feasible in terms of hard disk memory. — Ankur Soni, Apr 19 '19 at 11:38

How to convert MongoDb query into csv (excel format)

1 Answers1