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 — user6811411
– user6811411, Commented Oct 24, 2018 at 21:33

pandemic · Accepted Answer · 2018-10-24 21:42:00Z

2

$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.

answered Oct 24, 2018 at 21:42

pandemic

1,1952 gold badges23 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

TheCoon Over a year ago

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.

Ed Callahan · Accepted Answer · 2018-10-24 22:52:15Z

0

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

answered Oct 24, 2018 at 22:52

Ed Callahan

1896 bronze badges

Collectives™ on Stack Overflow

Powershell JSON pipeline expand multiple values into one column csv

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related