Convert the JSON Array values using Power Shell

Question

I have a JSON file with the below given sample structure. How can I convert this into CSV and get the content of CSV as the below given expected output?

{
   "code":"A123",
   "name":"ABC",
   "parties":[
      {
         "businessTeam":{
            "code":"B123",
            "name":"Plaza"
         },
         "TotalNumber":"1000"
      },
      {
         "businessTeam":{
            "code":"B125",
            "name":"Marina"
         },
         "TotalNumber":"2000"
      },
      {
         "businessTeam":{
            "code":"B130",
            "name":"Marriot"
         },
         "TotalNumber":"2500"
      }
   ]
}

Expected Output:

Code, Name,BusinessPartyCode,BusinessPartyName,Capacity

A123,ABC,B123,Plaza,1000

A123,ABC,B125,Marina,2000

A123,ABC,B130,Marriot,2500

I have tied with the below script and was able to extract the array values as a single delimiter concatenated values.

$deploy = Get-Content 'C:\psscripts\sample.json' -Raw | ConvertFrom-Json
$items = @()
foreach ($server in $deploy) {
    foreach ($item in $server) {
        $items += New-Object -TypeName PSObject -Property (
            [ordered]@{ 
                code = @($item.Code) -replace '"','#' -join '~'
                businessparty = @($item.parties.businessteam.code) -join '-'
                businesspartyName = @($item.parties.businessteam.name) -join '-'
                Capacity = @($item.parties.businessteamtotalnumber) -join '-' 
            }
        )
    }
} 
$items

-> output A123,ABC,B123-B125-B130,Plaza-Marina-Marriot,1000-2000-2500 Regards, Sandeep

Your json example appears invalid: **`ConvertFrom-Json: Conversion from JSON failed with error: After parsing a value an unexpected character was encountered: .. Path 'parties[0].businessTeam', line 5, position 61.`**. Please [edit](https://stackoverflow.com/review/suggested-edits/32182911) and paste some valid [mcve] data. — iRon, Jul 06 '22 at 07:41
As a general aside: [Try to avoid using the increase assignment operator (`+=`) to create a collection](https://stackoverflow.com/a/60708579/1701026) — iRon, Jul 06 '22 at 17:38

Santiago Squarzon · Answer 1 · 2022-07-06T18:05:25.153

You're missing an inner loop to expand the values of businessTeam:

Get-Content 'C:\psscripts\sample.json' -Raw | ConvertFrom-Json | ForEach-Object {
    foreach($item in $_.parties) {
        foreach($team in $item.businessTeam) {
            [pscustomobject]@{
                Code = $_.code
                Name = $_.name
                BusinessPartyCode = $team.code
                BusinessPartyName = $team.name
                Capacity = $item.TotalNumber
            }
        }
    }
} | Format-Table

Using the Json in question, the array of objects generated using this code would be:

Code Name BusinessPartyCode BusinessPartyName Capacity
---- ---- ----------------- ----------------- --------
A123 ABC  B123              Plaza             1000
A123 ABC  B125              Marina            2000
A123 ABC  B130              Marriot           2500

The script generates the above mentioned output in Powershell ISE result set, but when i convert it to csv, those array values are getting populated with the string "System.Object[]", instead of the actual data. Is there any solution for this? — Sandeep T, Jul 07 '22 at 08:21

Convert the JSON Array values using Power Shell

Code, Name,BusinessPartyCode,BusinessPartyName,Capacity

1 Answers1