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

CodePudding user response：

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

CodePudding user response：

Here is an adaptation from my previous answer that should be able to handle both Json presented on this question.

function Parse-Json {
    param(
        [parameter(ValueFromPipeline)]
        [object]$InputObject
    )

    process
    {
        $columns = $InputObject.Columns.Name
        foreach($row in $InputObject.Rows)
        {
            $out = [ordered]@{}; $i = 0
            foreach($element in $row)
            {
                $out[$columns[$i  ]] = $element
            }
            [pscustomobject]$out
        }
    }
}

The function can take input from pipeline so you could pipe the Json string to ConvertFrom-Json into this function:

PS /> $json1 | ConvertFrom-Json | Parse-Json

Computer        LastHeartbeat
--------        -------------
xxxxxxx.dev.org 1/19/2022 4:49:48 AM

PS /> $json2 | ConvertFrom-Json | Parse-Json

Computer       LastHeartbeat
--------       -------------
zzzzz.test.org 1/4/2022 9:06:45 AM
yyyy.dev.org   1/4/2022 9:06:30 AM
xxxx.prod.org  1/4/2022 9:06:08 AM
xxxx.dev.org   1/4/2022 9:06:04 AM

It would also work with multiple Json:

PS /> $json1, $json2 | ConvertFrom-Json | Parse-Json

Computer        LastHeartbeat
--------        -------------
xxxxxxx.dev.org 1/19/2022 4:49:48 AM
zzzzz.test.org  1/4/2022 9:06:45 AM
yyyy.dev.org    1/4/2022 9:06:30 AM
xxxx.prod.org   1/4/2022 9:06:08 AM
xxxx.dev.org    1/4/2022 9:06:04 AM