Converting Json Table

Question

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

It's unclear what the problem is or how to reproduce it - which exact array are you talking about? .tables.rows? Does it break if rows contains a single nested array? — Mathias R. Jessen
– Mathias R. Jessen, Commented Jan 19, 2022 at 12:03
@MathiasR.Jessen Thanks for checking, yes its break only one single nested array, script need to handle 1 or more nested array, but my script handles more than 1 array and it breaks when single nested array — avinash
– avinash, Commented Jan 19, 2022 at 12:10
@SantiagoSquarzon Apologies need to remove "tables": at the start — avinash
– avinash, Commented Jan 19, 2022 at 14:19
Note that convertto-json, which you are not showing, has a default depth of 2. I usually make it -depth 100. Using $affected_resources as both the input and output of the foreach loop is probably not a good idea. You could put back tables and put curly braces around the whole thing { } . — js2010
– js2010, Commented Jan 19, 2022 at 15:04
@SantiagoSquarzon that script works fine when there is single object in nested array and it fails when there are more than one object in nested array, as per my requirement one script need to handle both — avinash
– avinash, Commented Jan 20, 2022 at 2:56

Theo · Accepted Answer · 2022-01-19 16:48:32Z

1

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

edited Jan 19, 2022 at 16:48

answered Jan 19, 2022 at 16:39

Theo

61.9k8 gold badges28 silver badges48 bronze badges

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

1 Comment

avinash Over a year ago

Thanks for the help mate, its working both single and multiple objects in array

Collectives™ on Stack Overflow

Converting Json Table

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related