Export fields with multiple nested values from JSON to CSV

Question

I need to import some JSON data am getting into my database. I'd like to use PowerShell and figured out a little, but I need help getting the final piece together.

Here is my existing PowerShell script.

Get-Content -Path "t:\1.json"  | 
    ConvertFrom-Json | 
    Select-Object -expand data | 
    ConvertTo-Csv -NoTypeInformation |
    Set-Content "t:\1.csv"

It does everything but doesnot get the badges details out in single cav along with user name. My question is, how can I get all details in single csv?

Here is my JSON data:


{
   "data": [
      {
         "name": "Shiva",
         "email": "[email protected]",
         "organization": "Team",
         "badges": {
            "data": [
               {
                  "name": "AWS Certified Developer",
                  "id": "001"
               }
            ]
         },
         "id": "1001"
      },
      {
         "name": "Rudra",
         "email": "[email protected]",
         "organization": "Team",
         "badges": {
            "data": [
               {
                  "name": "Certified Google Associate Cloud Engineer",
                  "id": "006"
               }
            ]
         },
         "id": "1002"
      },
      {
         "name": "Alazar",
         "email": "[email protected]",
         "organization": "Team",
         "badges": {
            "data": [
               {
                  "name": "Google Cloud Data Engineer Certified",
                  "id": "007"
               },
               {
                  "name": "Google Certified Professional Cloud Network Engineer",
                  "id": "008"
               },
               {
                  "name": "AWS Solution Architect",
                  "id": "009"
               },
               {
                  "name": "Certified Google Associate Cloud Engineer",
                  "id": "006"
               }
            ]
         },
         "id": "1003"
      }
   ]
}

Welcome to stackoverflow.com. For any specific problem you are having please include a stackoverflow.com/help/minimal-reproducible-example. Please also read stackoverflow.com/help/how-to-ask and stackoverflow.com/help/dont-ask. Make sure that your questions are specific and not overly broad, see also stackoverflow.com/help/on-topic. Also, be sure to take the stackoverflow.com/tour (you get a badge!) — Daniel Björk
– Daniel Björk, Commented Aug 10, 2020 at 8:49
Have a look at this one: stackoverflow.com/questions/58031293/… — Daniel Björk
– Daniel Björk, Commented Aug 10, 2020 at 8:49
Or have a look at: gallery.technet.microsoft.com/scriptcenter/… — Daniel Björk
– Daniel Björk, Commented Aug 10, 2020 at 8:58

Theo · Accepted Answer · 2020-08-10 09:32:22Z

You need to add new rows of data for each badge a person can have.

Something like this:

(Get-Content -Path 't:\1.json' -Raw | ConvertFrom-Json).data | ForEach-Object {
    foreach ($badge in $_.badges.data) {
        $_ | Select-Object *,
                @{Name = 'badge_id';   Expression = { $badge.id }},
                @{Name = 'badge_name'; Expression = { $badge.name }} -ExcludeProperty badges
    }
} | Export-Csv -Path 't:\1.csv' -NoTypeInformation

After this, file 't:\1.csv' will look like this:

"name","email","organization","id","badge_id","badge_name"
"Shiva","[email protected]","Team","1001","001","AWS Certified Developer"
"Rudra","[email protected]","Team","1002","006","Certified Google Associate Cloud Engineer"
"Alazar","[email protected]","Team","1003","007","Google Cloud Data Engineer Certified"
"Alazar","[email protected]","Team","1003","008","Google Certified Professional Cloud Network Engineer"
"Alazar","[email protected]","Team","1003","009","AWS Solution Architect"
"Alazar","[email protected]","Team","1003","006","Certified Google Associate Cloud Engineer"

Collectives™ on Stack Overflow

Export fields with multiple nested values from JSON to CSV

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related