json - 如何将此 JSON 转换为 CSV？

Question

Get-Content data.json | ConvertFrom-Json | Where-Object { $_.type -eq "certificate" } | Select-Object -ExpandProperty description

我使用了 cmdlet ConvertFrom-Json，我可以选择我感兴趣的数据。但是一个对象包含嵌套数据。我希望在单独的列中没有方括号的存储、指纹和颁发者，以便之后我可以使用 cmdlet Export-Csv。

store:[AuthRoot] thumbprint:[02FAF3E291435468607857694DF5E45B68851868] issuer:[AddTrust External CA Root]
store:[AuthRoot] thumbprint:[039EEDB80BE7A03C6953893B20D2D9323A4C2AFD] issuer:[GeoTrust Primary Certification Authority - G3]
store:[AuthRoot] thumbprint:[0563B8630D62D75ABBC8AB1E4BDFB5A899B24D43] issuer:[DigiCert Assured ID Root CA]

标签： jsonpowershell

Answer 1

$Data = Get-Content "data.json" | ConvertFrom-Json | Where-Object { $_.type -eq "certificate" }

$Data | Foreach-Object {

$desc = $_ | Select-Object -ExpandProperty desc
$store = $_ | Select-Object -ExpandProperty desc2 | Select-Object @{Name="store"; Expression={ForEach-Object{($_ -split "store:")[1]} | ForEach-Object{($_ -split "thumbprint:")[0]} | ForEach-Object{($_ -replace "[\[\]]","")}}}
$thumbprint = $_ | Select-Object -ExpandProperty desc2 | Select-Object @{Name="thumbprint"; Expression={ForEach-Object{($_ -split "thumbprint:")[1]} | ForEach-Object{($_ -split "issuer:")[0]} | ForEach-Object{($_ -replace "[\[\]]","")}}}
$issuer  = $_ | Select-Object -ExpandProperty desc2 | Select-Object @{Name="issuer"; Expression={ForEach-Object{($_ -split "issuer:")[1]} | ForEach-Object{($_ -replace "[\[\]]","")}}}

New-Object -TypeName PSObject -Property @{
    "Description" = $desc
    "Store" = $store.store
    "Thumbprint (SHA1)" = $thumbprint.thumbprint
    "Issuer" = $issuer.issuer
    }
} | Select-Object "Description","Store","Thumbprint (SHA1)","Issuer" | Export-Csv -Path "C:\Users\<user>\Desktop\certificates.csv" -Delimiter ";" -NoTypeInformation