Converting Json Table

user_14177558 0
Viewed 138

I am trying to convert below Json table to Json array object, below json failed because of there is single nested json object, but its working when there are multiple objects in nested json

       [
          {
            "name": "PrimaryResult",
            "columns": [
              {
                "name": "Computer",
                "type": "string"
              },
              {
                "name": "LastHeartbeat",
                "type": "datetime"
              }
            ],
            "rows": [
              [
                "xxxxxxx.dev.org",
                "2022-01-19T04:49:48.937Z"
              ]
            ]
          }
        ]

Expected output is as below

                          [
                           {
                            "Computer":  xxxxxxx.dev.org",
                            "LastHeartbeat":  "2022-01-19T04:49:48.937Z"
                           }
                         ]

I have tried with the below Json script it works when there are multiple objects in array

         [
          {
            "name": "PrimaryResult",
            "columns": [
              {
                "name": "Computer",
                "type": "string"
              },
              {
                "name": "LastHeartbeat",
                "type": "datetime"
              }
            ],
            "rows": [
              [
                "zzzzz.test.org",
                "2022-01-04T09:06:45.44Z"
              ],
              [
                "yyyy.dev.org",
                "2022-01-04T09:06:30.233Z"
              ],
              [
                "xxxx.prod.org",
                "2022-01-04T09:06:08.893Z"
              ],
              [
                "xxxx.dev.org",
                "2022-01-04T09:06:04.667Z"
              ]
            ]
          }
        ]

I have tried with below powershell script
=============================================
$TriggerMetadata = Get-Content .\test4.json | ConvertFrom-Json
$affected_resources = $TriggerMetadata.tables
$resources = 
    ForEach($Row in $affected_resources.rows)
    {
        $TmpHash = [Ordered]@{}
        For($i = 0; $i -lt $Row.Length; $i++ )
        {
            $TmpHash.Add($affected_resources.columns.name[$i], $Row[$i] )
        }
        [PSCustomObject]$TmpHash
    }
$body = $resources | ConvertTo-Json -Depth 100
$Body

Getting below error

Exception calling "Add" with "2" argument(s): "Key cannot be null.
Parameter name: key"
At line:22 char:13
+             $TmpHash.Add($affected_resources.columns.name[$i], $Row[$ ...
+             ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : ArgumentNullException
powershell
1 Answers

PowerShell 'unravels' arrays when there is only one element in it.
In this case that produces the error you encounter you can fix by using the , unary comma operator which will wrap the single element of the input array into another single element array.

When PowerShell unwraps that, the result is the original array of row values from the json file

Assuming you have the JSON converted in a variable $json:

$headers = $json.columns.name
# if there is only one 'rows 'element, wrap it in an array by prefixing with comma
if ($json.rows[0] -is [array]) { $rows = $json.rows } else { $rows = ,$json.rows }
$resources = $rows | ForEach-Object {
    $hash = [ordered]@{}
    for ($i = 0; $i -lt $headers.Count; $i++) {
        $hash[$headers[$i]] = $_[$i]
    }
    [PsCustomObject]$hash
}

Output:

{
    "Computer":  "zzzzz.test.org",
    "LastHeartbeat":  "2022-01-04T09:06:45.44Z"
}

If you really do need the square brackets around that, you can do the test as before and enclose the result in '[..]'

if ($json.rows[0] -is [array]) { 
    $body = $resources | ConvertTo-Json -Depth 100
}
else {
    $body = "[ {0} ]" -f ($resources | ConvertTo-Json -Depth 100)
}

Output:

[ {
    "Computer":  "zzzzz.test.org",
    "LastHeartbeat":  "2022-01-04T09:06:45.44Z"
} ]
user_98986430
Related