Powershell nested json to repeating csv

Question

Kind of similar to Convert nested JSON array into separate columns in CSV file but instead of flattened csv (ie discipline_01, discicpline_01) exporting to multiple lines of the csv:

{
"data": [{
    "attributes": {
        "id": 10011,
        "title": "Test procedure",
        "slug": "slug",
        "url": "http://test.test",
        "disciplines": [
            "discipline_a",
            "discipline_b",
            "discipline_c"
        ]
    }
}]
}

export to

"id","title","slug","url","discipline"
"10011","Test procedure","slug","http://test.test","discipline_a"
"10011","Test procedure","slug","http://test.test","discipline_b"
"10011","Test procedure","slug","http://test.test","discipline_c"

Thanks to Export fields with nested values from JSON to CSV I've gotten this far:

$foo = invoke-restmethod $restquery -headers $headers
$foo | 
select -ExpandProperty data | 
select -ExpandProperty attributes |
select id, title, slug, url, disciplines | 
   foreach { 
        $_.disciplines = $_disciplines -join ' ' 
        $_ | 
export-csv c:\outfile.csv -notypeinformation

This gives me

"10011","Test procedure","slug","http://test.test","discipline_a discipline_b discipline_c"

But no clue how to get it to:

"id","title","slug","url","discipline"
"10011","Test procedure","slug","http://test.test","discipline_a"
"10011","Test procedure","slug","http://test.test","discipline_b"
"10011","Test procedure","slug","http://test.test","discipline_c"

Any help is appreciated.

user6811411 · Accepted Answer · 2019-05-31 22:26:40Z

0

While your posted Json is invalid,
this might do:

## Q:\Test\2019\05\31\SO_56401395.ps1
$foo = invoke-restmethod $restquery -headers $headers

$Data = $foo.data.attributes | ForEach-Object {
    foreach ($discipline in $_.disciplines}(
        [PSCustomObject]@{
            id         = $_.id
            title      = $_.title
            slug       = $_.slug
            url        = $_.url
            discipline = $discipline
        }
    }
}
$Data | Export-Csv c:\outfile.csv -NoTypeInformation

answered May 31, 2019 at 22:26

user6811411

Sign up to request clarification or add additional context in comments.

1 Comment

mobious74 Over a year ago

That did it! And learned a lot! I also edited my json in original question to be valid. Thanks!

Collectives™ on Stack Overflow

Powershell nested json to repeating csv

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related