title: Expand Multi-level Powershell nested hash tables from JSON api call
body: <p>I am having an issue with a JSON API call using Powershell, that returns a multi-level nested array. Using the standard convertfrom-json &amp; convertto-json doesn't give me the full results. I just get a ...</p>
  
  <p>I have done a lot of research on this and found the following <a href="https://stackoverflow.com/questions/30415162/format-a-nested-hashtable">link</a>  on stack for expanding the nested hash tables, and this is about the best format that I have gotten. For reference, I am trying to insert employee records into SQL Server. I have a JSON function for doing so, and need to get the JSON response in the expanded format. There are some security aspects, that won't allow me to post the actual detail so I am trying to provide an example of some issues that I am having. Keep in mind the response is for 1 employee record out of hundreds overall, and in SQL will be one table with a row per employee. </p>
  
  <pre><code>$json1 = Invoke-webRequest -Uri $Workeruri -Certificate $cert -Headers $WorkerHeader | convertfrom-json 
  
  $workers = $json1.workers | select -property *
  
  $workers | format-table Name, @{n='Value'; e={
      if ($_.Value -is [Hashtable]) {
          $ht = $_.Value
          $a = $ht.keys | sort | % { '{0}={1}' -f $_, $ht[$_] }
          '{{{0}}}' -f ($a -join ', ')
      } else {
          $_.Value
      }
  }}
  
  $workers
  </code></pre>
  
  <p>I have also tried:</p>
  
  <pre><code>$workers | convertto-json
  </code></pre>
  
  <p>and</p>
  
  <pre><code>$workers = json1.workers | foreach-object {$_}
  </code></pre>
  
  <p>The data returned comes back like this:</p>
  
  <pre><code>associateOID          : XXXXXXXXXXXXXX
  workerID              : @{idValue=XXXXXXX}
  person                : @{governmentIDs=System.Object[]; legalName=; birthDate=0000-00-00; legalAddress=; genderCode=; maritalStatusCode=; socialInsurancePrograms=System.Object[]; tobaccoUserIndicator=False; raceCode=; customFieldGroup=}
  workerDates           : @{originalHireDate=2015-03-09; terminationDate=2016-03-18}
  workerStatus          : @{statusCode=}
  businessCommunication : @{emails=System.object[]}
  workAssignments       : {@{itemID=xxxxxx; occupationalclassification=System.Object[]; etc}}
  </code></pre>
  
  <p>I need it to come back with all of the columns on the left side, utilizing the "AssociateOID" as the key identifier for the individual. I have previously gotten the JSON response to come back completely expanded using this format but it wasn't working with the import into SQL Server or looking very nice like the Google Postman response:</p>
  
  <pre><code>$json1 = Invoke-webRequest -Uri $Workeruri -Certificate $cert -Headers $WorkerHeader | format-list -property *
  </code></pre>