Convert JSON to CSV using PowerShell

Question

I have a sample JSON-formatted here which converts fine if I use something like: https://konklone.io/json/

I've tried the following code in PowerShell:

(Get-Content -Path $pathToJsonFile | ConvertFrom-Json) 
| ConvertTo-Csv -NoTypeInformation 
| Set-Content $pathToOutputFile

But the only result I get is this:

{"totalCount":19,"resultCount":19,"hasMore":false,"results":

How do I go about converting this correctly in PowerShell?

Answer 1

By looking at just (Get-Content -Path $pathToJsonFile) | ConvertFrom-Json it looks like the rest of the JSON is going in to a results property so we can get the result I think you want by doing:

((Get-Content -Path $pathToJsonFile) | ConvertFrom-Json).results |
    ConvertTo-Csv -NoTypeInformation |
    Set-Content $pathToOutputFile

FYI you can do ConvertTo-Csv and Set-Content in one move with Export-CSV:

((Get-Content -Path $pathToJsonFile) | ConvertFrom-Json).results |
    Export-CSV $pathToOutputFile -NoTypeInformation

Answer 2

You have to select the results property inside your CSV using the Select-Object cmdlet together with the -expand parameter:

Get-Content -Path $pathToJsonFile  | 
    ConvertFrom-Json | 
    Select-Object -expand results | 
    ConvertTo-Csv -NoTypeInformation |
    Set-Content $pathToOutputFile