Convert JSON to CSV using PowerShell

Question

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

I 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 the following:

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

How do I do it right in PowerShell?

Answer 1

Looking only at (Get-Content -Path $pathToJsonFile) | ConvertFrom-Json, it seems that the rest of the JSON is included in the property results, so we can get the result, which I think you want to do:

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

FYI you can do ConvertTo-Csvand Set-Contentin one move with the help of Export-CSV:

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

Answer 2

results CSV Select-Object -expand:

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

Answer 3

I got my json from the REST website and found that the following worked:

Invoke-WebRequest -method GET -uri $RemoteHost -Headers $headers 
 | ConvertFrom-Json 
 | Select-Object -ExpandProperty  <Name of object in json>
 | ConvertTo-Csv -NoTypeInformation 
 | Set-Content $pathToOutputFile

I end up with a perfectly formatted csv file