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": "Shiva@example.com",
         "organization": "Team",
         "badges": {
            "data": [
               {
                  "name": "AWS Certified Developer",
                  "id": "001"
               }
            ]
         },
         "id": "1001"
      },
      {
         "name": "Rudra",
         "email": "Rudra@example.com",
         "organization": "Team",
         "badges": {
            "data": [
               {
                  "name": "Certified Google Associate Cloud Engineer",
                  "id": "006"
               }
            ]
         },
         "id": "1002"
      },
      {
         "name": "Alazar",
         "email": "Alazar@example.com",
         "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"
      }
   ]
}

Answer 1

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","Shiva@example.com","Team","1001","001","AWS Certified Developer"
"Rudra","Rudra@example.com","Team","1002","006","Certified Google Associate Cloud Engineer"
"Alazar","Alazar@example.com","Team","1003","007","Google Cloud Data Engineer Certified"
"Alazar","Alazar@example.com","Team","1003","008","Google Certified Professional Cloud Network Engineer"
"Alazar","Alazar@example.com","Team","1003","009","AWS Solution Architect"
"Alazar","Alazar@example.com","Team","1003","006","Certified Google Associate Cloud Engineer"