Powershell json to csv complex json file

Question

I'm trying to convert a complex json file into a csv file. I've tried a few things now and this query worked best, unfortunately I can't get the product to be displayed The product to be displayed would be the following in the json file "PRD_GV_BCA" Unfortunately, I only get the other values displayed with the query from one product and not from another. (Of course, also enter a product in the code below, don't know how I can use a placeholder there or that changes, I'm not that good with Powershell yet)

Hope you have an approach how I can do it differently

current result from Script current result

that how it should looks like that how it should look like

$pathToJsonFile = "<Pfad zum Skript>\jsontocsv\tjson.json"
$pathToOutputFile = "<pfad zur Excel Datei>\jsontocsv\converts.csv"
((Get-Content -Path $pathToJsonFile) | ConvertFrom-Json)  | ForEach-Object {
$Produkt = $_
$deckung += $_.PRD_GV_BCA | ForEach-Object {
[pscustomobject] @{
    'Produkt' = $Produkt
    'iconCode' = $_.iconCode
    'labelKey' = $_.labelKey
    'descriptionKey' = $_.descriptionKey
    'gedeckt' = $_.gedeckt
 }

}
write-host $deckung
}
$deckung | Export-CSV $pathToOutputFile -NoTypeInformation

thats a part from the Input File, there more entries (roundabout 40 products and there sub entries

 {
        "PRD_GV_BCA": [{
                "iconCode": "custom:KVG.ArztwahlManagedCare",
                "labelKey": "ArztwahlManagedCare.label",
                "descriptionKey": "KVG.ArztwahlManagedCare.description",
                "gedeckt": true
        },
            {
                "iconCode": "custom:KVG.Franchise",
                "labelKey": "Franchise.label",
                "descriptionKey": "KVG.Franchise.description",
                "gedeckt": true
            },          {
                "iconCode": "custom:KVG.VVG.Versichertenkarte",
                "labelKey": "VersichertenkarteKVG.label",
                "descriptionKey": "KVG.VersichertenkarteKVG.description",
                "gedeckt": true
            }
        ],
        "PRD_GV_BEU": [{
                "iconCode": "custom:EGK-KVG_EU",
                "labelKey": "EU.label",
                "descriptionKey": "KVG.EU.description",
                "gedeckt": true
            },
            {
                "iconCode": "custom:KVG.VVG.Versichertenkarte",
                "labelKey": "VersichertenkarteKVG.label",
                "descriptionKey": "KVG.VersichertenkarteKVG.description",
                "gedeckt": true
            }
        ]
    }

Update: with that Query i get the Product but not the rest. But also only one product

$pathToJsonFile = "<Pfad zum Skript>\jsontocsv\tjson.json"
$pathToOutputFile = "<Pfad zum Skript>\jsontocsv\converts.csv"
((Get-Content -Path $pathToJsonFile) | ConvertFrom-Json) | ForEach-Object {
$test = ($_ |out-string).trim()
$test2 = $test | ForEach-Object {
    [pscustomobject] @{
        'Produkt' = $test
        'iconCode' = $_.iconCode
        'labelKey' = $_.labelKey
        'descriptionKey' = $_.descriptionKey
        'gedeckt' = $_.gedeckt
        }
    }
}
$test2 | Export-CSV $pathToOutputFile -NoTypeInformation

result of the query where product is shown

Thanks for your help / support regards Chris

Answer 1

By using Out-String, you get a multiline string which is no longer a set of objects with the data.

For this you will need to do some more loops over the objects properties like below:

$pathToJsonFile   = "<Pfad zum Skript>\jsontocsv\tjson.json"
$pathToOutputFile = "<Pfad zum Skript>\jsontocsv\converts.csv"

$deckung = Get-Content -Path $pathToJsonFile -Raw | ConvertFrom-Json | ForEach-Object {
    foreach ($item in $_ ) {
        foreach ($produkt in $item.PsObject.Properties) {
            foreach ($detail in $produkt.Value) {
                [PsCustomObject] @{
                    Produkt        = $produkt.Name
                    iconCode       = $detail.iconCode
                    labelKey       = $detail.labelKey
                    descriptionKey = $detail.descriptionKey
                    gedeckt        = $detail.gedeckt
                }
            }
        }
    }
}

# output to CSV file
$deckung | Export-Csv $pathToOutputFile -NoTypeInformation -UseCulture

Note: by appending switch -UseCulture to the Export-Csv cmdlet, it will create a csv using the delimiter character your Excel expects, so you can simply double-click the file to get this result: