Powershell JSON pipeline expand multiple values into one column csv

Question

I'm trying to automate some data pipelines with Powershell, but I'm kinda stuck with converting a JSON list to a single cell per row in a CSV file. Hope some of you can help me out.

The JSON I get looks like the following:

{"result": [
    {
      "uid": "1",
      "EducationHistory": []
    },
    {
      "uid": "2",
      "EducationHistory": []
    },
    {
      "uid": "3",
      "EducationHistory": []
    },
    {
      "uid": "4",
      "EducationHistory": {
        "10466632": {
          "euid": 10466632,
          "degree": "Highschool",
          "educationLevel": null
        },
        "10466634": {
          "euid": 10466634,
          "degree": "Law",
          "educationLevel": "batchelor"
        },
        "10466635": {
          "euid": 10466635,
          "degree": "Law",
          "educationLevel": "master"
        }
      }
    },
    {
      "uid": "5",
      "EducationHistory": {
        "10482462": {
          "euid": 10482462,
          "degree": "IT",
          "educationLevel": "master"
        }
      }
    }
  ]
}

What I want to do is collect the educationLevels per uid in one column. So something like this:

uid | educationLevel
----+------------------
1   | 
2   | 
3   |
4   | barchelor, master
5   | master

Normally I would like Expandproperty to get down to a lower level, but this doesn't work for this case because every EducationHistory entry is behind a euid for that specific entry. Expanding every single one of them like in the example below isn't workable because of the number of records.

So I think I need something of a loop, but I don't know how. Hope you can help me. First post here and a Powershell newbie, so I hope my question is clear. Please let me know if you need more info.

The code for one entry, as example:

$json = Get-content -raw -path C:\TEMP\File.json
   (ConvertFrom-Json -InputObject $json).result  |
   Select-Object uid, 

    #Expand one of the entries:
    @{Name = "Edu.Level";E={$_.EducationHistory | Select-Object - 
    expandproperty 10466632 |Select-Object -expandpropert degree }}   | 
    Format-Table

Possible duplicate of [Iterating through JSON File PowerShell](https://stackoverflow.com/questions/33520699/iterating-through-json-file-powershell) — , Oct 24 '18 at 21:33

score 2 · Accepted Answer · answered Oct 24 '18 at 21:42

$content = Get-Content .\test.json
$result = ($content | ConvertFrom-Json).result

$totalResult = @()

foreach($res in $result) {

    $tempArray = @()

    if($res.EducationHistory -ne $null) {
        $properties = $res.EducationHistory | Get-Member -MemberType NoteProperty
        foreach($property in $properties) {

            $eduLevel = $res.EducationHistory.$($property.Name).educationLevel

            if(![String]::IsNullOrEmpty($eduLevel)) {
                $tempArray += $eduLevel
            }
        }
    }

    $totalResult += [PSCustomObject]@{
        uid = $res.uid
        educationLevel = $tempArray -join ", "
    }

}

$totalResult

This will output desired result for the input you have provided. The trickiest part is the value of EducationHistory property. You have to use Get-Member cmdlet (see Get-Help Get-Member) to get the properties of the current object in loop. Then using the name of the property to access the educationLevel.

This did the trick! Thanks you very much! The code and explanation was really helpful and I could tweak it and also use it for other similar problems. — TheCoon, Oct 26 '18 at 10:25

score 0 · Answer 2 · answered Oct 24 '18 at 22:52

Your first question, my first answer I believe :) Similar to the last answer. You need to jump through the hoop of finding the object names in EducationalHistory to reference them.

$json = (Get-content C:\TEMP\File.json | ConvertFrom-Json).result

$results = @()
foreach ( $u in $json)
{
    foreach ( $h in $u.EducationHistory)
    {
        $results += $h.PSObject.properties.Name | ForEach-Object{new-object PSObject -property @{ uid=$u.uid; degree=$h.$_.degree}}
    }
}

$results | ConvertTo-Csv | select -skip 1

Powershell JSON pipeline expand multiple values into one column csv

2 Answers2