Concatenate Data in Nested JSON file to CSV

I get a JSON file with Server info in it and I’m trying to concatenate the output into one output.

Here is the output I get:

Here is the output I need:

Here is my JSON File:

[
  {
      "Server Name": "DC01",
      "Operating System": "Windows Server 2016",
      "Service": "Domain Controller",
      "IPs": [
     {
        "IP": "10.2.0.4"
     }
   ]
 },
 {
      "Server Name": "WEB01",
      "Operating System": "Windows Server 2016",
      "Service": "Web Server",
       "IPs": [
       {
          "IP": "10.3.0.100"
       },
       {
          "IP": "10.3.0.101"
       }
    ]
  }
]

Here is the PowerShell:

$JsonFile = "C:\Tmp\Servers-JSON.json"
$OutputJson = "C:\Tmp\Servers-CSV.csv"
$Servers = @()
((Get-Content -Path $JsonFile) | ConvertFrom-Json) | ForEach-Object {
$sname = $_."Server Name"
$os = $_."Operating System"
$Service = $_.Service
    $Servers += $_.IPs | ForEach-Object {
        [pscustomobject] @{
        "Server Name" = $sname
        "Operating System" = $os
        "Service" = $Service
        "IP" = $_.IP -join ','
            }
        }
}
$Servers | Export-Csv $OutputJson -NoTypeInformation

>Solution :

A simplified example:

@'
[
  {
      "Server Name": "DC01",
      "Operating System": "Windows Server 2016",
      "Service": "Domain Controller",
      "IPs": [
     {
        "IP": "10.2.0.4"
     }
   ]
 },
 {
      "Server Name": "WEB01",
      "Operating System": "Windows Server 2016",
      "Service": "Web Server",
       "IPs": [
       {
          "IP": "10.3.0.100"
       },
       {
          "IP": "10.3.0.101"
       }
    ]
  }
]
'@ | ConvertFrom-Json |
  ForEach-Object {
    $_.IPs = $_.IPs.IP -join ', '
    $_ # Output the modified object.
  } | 
      ConvertTo-Csv  # Replace this with your Export-Csv call

The above yields:

"Server Name","Operating System","Service","IPs"
"DC01","Windows Server 2016","Domain Controller","10.2.0.4"
"WEB01","Windows Server 2016","Web Server","10.3.0.100, 10.3.0.101"

• Since you’re not changing the names and number of properties, you can directly modify each input object that ConvertFrom-Json parses your JSON into.

• $_.IPs.Ip uses member enumeration to extract all IP addresses contained in the array of objects stored in $_.IPs, which are then joined with ', ' to form a single string and assigned back to the .IPs property.