to print nested json array values into csv using MongoDB

Question

I want to output the nested json array into csv.

sample.json

{
   "DocId":"ABC",
   "User":[
      {
         "Id":1234,
         "Username":"sam1234",
         "Name":"Sam",
         "ShippingAddress":{
            "Address1":"123 Main St.",
            "Address2":null,
            "City":"Durham",
            "State":"NC"
         }
      },
      {
         "Id":5678,
         "Username":"sam5678",
         "Name":"Sam",
         "ShippingAddress":{
            "Address1":"5678 Main St.",
            "Address2":null,
            "City":"Durham",
            "State":"NC"
         }
      }
   ]
}

enter code here

Above is the sample file, DocID must not be printed, and output in csv must be only for array contents

Id Username Name ShippingAddress

1234 sam1234 Sam 123 Main St.Durham NC

5678 sam5678 Sam 5678 Main St.Durham NC

How to print with headers, and with out headers in csv

Viraj · Accepted Answer · 2014-10-08 09:57:17Z

2

One way to do is to do it in two steps

Perform aggregation on this collection and change the structure of the collection docs and output them in another collection
Use mongoexport to export the collection created in step 1 as CSV [This step can be used directly ^-^].

For step 1, Lets say I have db -> test and collection -> stack, so aggregation query is:

db.stack.aggregate([
    { $unwind:"$User"},
    { $project : { Id : "$User.Id" , Username:"$User.Username", Name:"$User.Name", ShippingAddress:"$User.ShippingAddress", _id:0} },
    { $out: "result" }
])

For step 2, use mongoexport terminal utility:

mongoexport --db test --collection result --csv --fields "Id,Username,Name,ShippingAddress" --out file.csv

edited Oct 8, 2014 at 9:57

answered Oct 8, 2014 at 9:30

Viraj

3572 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user4119502 Over a year ago

In the above output is it possible to print only data in the csv file i.e with out headers.

Collectives™ on Stack Overflow

to print nested json array values into csv using MongoDB

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related