Convert Json With Nested Arrays to CSV

MikeRD 

It's pretty much in line with what I had above.

Here's a more complete, yet still basic script. I simply named this Get-StudentsFromJson.ps1 so I could provide some use examples below but you can call it what you like.

Use example 1: Providing the path to a properly-formatted JSON file.

.\Get-StudentsFromJson.ps1 -Path .\json1.txt

Use example 2: Piping in the text.

Here, I'm taking it from a file but it could just as easily come from any command that returns peoperly-formatted JSON, such as Invoke-Method.

Get-Content -Path .\json.txt -Raw | .\Get-StudentsFromJson.ps1

The script itself (again, this is nothing fancy and I'm stopping at converting it to object notation as I fully expect you know how to then further convert it to CSV or whatever else you'd like to do with the object(s).)

[cmdletbinding(DefaultParameterSetName="ByPath")]
param(
    [parameter(Mandatory=$true, ParameterSetName="ByPath")][ValidateNotNullOrEmpty()][string] $Path
    , [parameter(Mandatory=$true, ParameterSetName="ByPipe", ValueFromPipeline=$true, Position=0)][ValidateNotNullOrEmpty()][string[]] $RawText
)

# If ByPath is the entry point, check that the specified file actually exists.
if ($Path)
{
    if (Test-Path -Path $Path)
    {
        (Get-Content -Path $Path -Raw | ConvertFrom-Json).results.rows | ForEach-Object {
            $ht = [System.Collections.Hashtable]::new();
            $_.columns | ForEach-Object { $ht.Add($_.name, $_.value); }
            [PSCustomObject]$ht;
        };
    }
    else
    {
        throw [System.IO.FileNotFoundException]::new();
    }

}
else
{
    ($RawText | ConvertFrom-Json).results.rows | ForEach-Object {
        $ht = [System.Collections.Hashtable]::new();
        $_.columns | ForEach-Object { $ht.Add($_.name, $_.value); }
        [PSCustomObject]$ht;
    };
}

It's worth noting I've tried to chain pipes together as much as possible to promote early object release. You can store conversions in things like variables but that can manifest as memory issues where very large input sources are used.

That's not relevant where you're only talking about 500 - or even thousands - of rows, but if you get into the hundreds of thousands, it's a different story. Anyway, I digress.

Note

With your JSON example, it's missing the outer prefixed "{" and closing "}", which in turn causes ConvertFrom-Json to throw a hissy-fit.

If you add that in to get a properly formatted response then you get this as example output.

ec_phonetype    :
ec_lastname     :
ec_phone        :
ec_sortorder    :
ec_firstname    :
student_user_id : 1234

ec_phonetype    :
ec_lastname     :
ec_phone        :
ec_sortorder    :
ec_firstname    :
student_user_id : 5678

Cheers,

Lain