Concatenate Data in Nested JSON file to CSV

Question

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:

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
WEB01	Windows Server 2016	Web Server	10.3.0.101

Here is the output I need:

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

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

mklement0 · Accepted Answer · 2022-03-10T23:11:10.733

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-access 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.

Concatenate Data in Nested JSON file to CSV

1 Answers1