CODEWITHSUNDEEP
mongodb
user4119502
user4119502

Reputation:

to print nested json array values into csv using MongoDB

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

Upvotes: 0

Views: 746

Answers (1)

Viraj
Viraj

Reputation: 357

One way to do is to do it in two steps

  1. Perform aggregation on this collection and change the structure of the collection docs and output them in another collection
  2. 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

Upvotes: 2

Related Questions