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?

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?

• json
• powershell
• csv

2 Answers 2

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

Your string is a JSON.
There are multiple way to manipulate it.

If you are using Powershell Core/7.x you can use this

# Replace with however you are getting the JSON.   
$JsonString = '{"TownA":{"female":512,"male":468},"TownB":{"female":748,"male":845}}'

$JsonHashtable = $JsonString | ConvertFrom-Json -AsHashtable

$ConvertedArray = foreach ($Key in $JsonHashtable.Keys) {
    foreach ($Gender in $JsonHashtable.$Key.Keys) {
        # casting to [Ordered] is actually superfluous unless you REALLY need
        # the columns being in a specific order.  
        [ordered]@{
            'town'   = $Key
            'value'  = $JsonHashtable.$key.$Gender
            'gender' = $Gender
        }
    }
}

$ConvertedArray | Export-Csv -Path $CsvPath