How to convert nested JSON to CSV powershell script

Question

I'm running into an issue where my Powershell code isn't working.

 $pathToJsonFile='C:\Users\<redacted>\'
 $InputFile= Read-Host -prompt 'Specify a target file'
 $OutputFile= Read-Host -prompt 'Specify a destination file'
 ((Get-Content -Path $pathToJsonFile$InputFile) | ConvertFrom-Json -Depth 64).results | ConvertTo-Csv -NoTypeInformation | Set-Content $pathToJsonFile$OutputFile

The error I keep getting is:

4 | … om-Json -Depth 64).results | ConvertTo-Csv -NoTypeInformation | Set-C … | ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ | Cannot bind argument to parameter 'InputObject' because it is null.

I'm not sure what the issue is since I'm piping one result into another. Referencing this post: Convert JSON to CSV using PowerShell

I'm also not sure what Depth is used for because I have two-layers of nested JSON I'm trying to get to do the conversion correctly.

Sounds like the json document doesn't have a results top-level property. — Mathias R. Jessen
– Mathias R. Jessen, Commented Apr 19, 2023 at 15:21
So, Depth is just how many nested layers do you want to go into the JSON. To your main point, you won't be able to directly convert nested JSON into csv. You will have to do some data manipulation to do this. — Patrick Mcvay
– Patrick Mcvay, Commented Apr 19, 2023 at 15:22
{ "image_name": "", "docker_image_id": "", "tag": "", "platform": "docker", "findings": [ { "nvdFinding": { "published_date": "", "cpe": [], "remediation": "", "references": [ "sourceware.org/bugzilla/show_bug.cgi?id=28768", "lists.debian.org/debian-lts-announce/2022/10/msg00021.html" ] }, "packages": [ { "name": "libc-bin", "version": "2.28-10+deb10u1", "type": "linux" } ] }, ... } — murkywaters
– murkywaters, Commented Apr 19, 2023 at 15:43

Geoff · Accepted Answer · 2023-04-21 12:57:29Z

The issue is that the original command addresses a results property in the deserialized object, which doesn't appear to exist in your schema. If the objective is to project a different property, say the packages array in the first findings element, to a CSV, you'd want to change your command to something like (note I added the quotes around the paths too):

$pathToJsonFile='C:\Users\<redacted>\'
$InputFile= Read-Host -prompt 'Specify a target file'
$OutputFile= Read-Host -prompt 'Specify a destination file'
((Get-Content -Path "$pathToJsonFile$InputFile") | ConvertFrom-Json -Depth 64).findings[0].packages | ConvertTo-Csv -NoTypeInformation | Set-Content "$pathToJsonFile$OutputFile"

More Info

Adjusting the original command:

((Get-Content -Path ".\test.json") | ConvertFrom-Json -Depth 64).results | ConvertTo-Csv -NoTypeInformation | Set-Content .\test.csv

Where test.json contains:

{
    results: [
        { name: "Bill", age: 38 },
        { name: "Jeff", age: 39 },
        { name: "Ruby", age: 40 }   
    ]
}

Will yield a new file test.csv which contains:

"name","age"
"Bill","38"
"Jeff","39"
"Ruby","40"

Collectives™ on Stack Overflow

How to convert nested JSON to CSV powershell script

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related