Powershell: formatted String(2Dim Array) to CSV

Question

I have a function to return a json string in this format:

{"TownA":{"female":512,"male":468},"TownB":{"female":748,"male":845}}

I'd like to append the data to a CSV:

town, value, gender   
TownA,  512,   female 
TownA,  468,   male  
TownB,  748,   female  
TownB,  845,   male

I think I need to first convert it to a custom object, like this:

$Line= [pscustomobject] @{
        'Town' = [STRING]""
        'Value' = [FLOAT]""
        'sex' = [STRING]""
        }

But I'm not sure. How can append this data to my csv?

Answer 1

What you got from your function is a Json string, you can use ConvertFrom-Json to parse it into object, from there you will have to enumerate the properties and nested properties to get the desired output:

$json = ConvertFrom-Json '{"TownA":{"female":512,"male":468},"TownB":{"female":748,"male":845}}'
$json.PSObject.Properties | ForEach-Object {
    foreach ($property in $_.Value.PSObject.Properties) {
        [pscustomobject]@{
            Town   = $_.Name
            Value  = $property.Value
            Gender = $property.Name
        }
    }
} # Can pipe to Export-Csv from here