1

I am having an issue with a JSON API call using Powershell, that returns a multi-level nested array. Using the standard convertfrom-json & convertto-json doesn't give me the full results. I just get a ...

I have done a lot of research on this and found the following link 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. 

$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

I have also tried:

$workers | convertto-json

and

$workers = json1.workers | foreach-object {$_}

The data returned comes back like this:

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}}

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:

$json1 = Invoke-webRequest -Uri $Workeruri -Certificate $cert -Headers $WorkerHeader | format-list -property *
Jumpman
  • 67
  • 1
  • 4
  • Is the question about fully expanding `$workers` into JSON? – Jason Boyd Jun 29 '16 at 22:47
  • I ended up getting everything expanded using PScustomObject. However, now I am unable to get a new row per employee. I pipe the custom object to format-table autosize but each column looks like {xxxxx, xxxx, xxx} and doesn't do a new row. – Jumpman Jun 30 '16 at 21:37

1 Answers1

0

You mean you want to get a JSON file that all objects/properties are expanded, right? Use the -Depth option of ConvertTo-Json to write a JSON file properly. Default value of -Depth option is 2.

$workers | ConvertTo-Json -Depth 100

Specify the appropriate depth according to your JSON files.

SATO Yusuke
  • 1,600
  • 15
  • 39