converting Json table using PowerShell

Question

I am trying to convert below Json table to Json object

    "tables": [
      {
        "name": "PrimaryResult",
        "columns": [
          {
            "name": "TimeGenerated",
            "type": "datetime"
          },
          {
            "name": "Computer",
            "type": "string"
          },
          {
            "name": "EventID",
            "type": "int"
          },
          {
            "name": "EventLevelName",
            "type": "string"
          },
          {
            "name": "RenderedDescription",
            "type": "string"
          }
        ],
        "rows": [
          [
            "2022-01-10T09:25:41.837Z",
            "Mycomputer",
             2974,
            "Error",
            "The attribute value provided is not unique in the forest or partition. Attribute: servicePrincipalName Value=MSSQL/xxxxxxx.dev.com CN=xxxxxxxxx,OU=DBS,OU=SVC,OU=Servers,OU=UK,DC=dev,DC=xxxxx,DC=xxxxxx,DC=org Winerror: 8647   See http://go.microsoft.com/fwlink/?LinkID=279782 for more details on this policy. "
          ]
        ]
      }
    ]

Expected output is as below, i have tried hard but no found how to do it

  [ 
   {
    "TimeGenerated": "2022-01-10T09:25:41.837Z",
    "Computer": "Mycomputer",
    "EventID": "2974",
    "EventLevelName": "Error",
    "Rendered Description": "The attribute value provided is not unique in the forest or partition. Attribute: servicePrincipalName Value=MSSQL/xxxxxxx.dev.com CN=xxxxxxxxx,OU=DBS,OU=SVC,OU=Servers,OU=UK,DC=dev,DC=xxxxx,DC=xxxxxx,DC=org Winerror: 8647   See http://go.microsoft.com/fwlink/?LinkID=279782 for more details on this policy."
  }
]

I have tried below script but not worked as expected, Need to get result as mentioned above

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

please update the question with the code you commented, and delete the comment — Avshalom
– Avshalom, Commented Jan 10, 2022 at 13:44

Santiago Squarzon · Accepted Answer · 2022-01-10 14:38:59Z

0

This worked for me adding a new set of rows, it should scale fine with more rows however it's hard to tell without testing it more.

$columns = $json.tables.columns.Name
$newObject = $json.tables.rows.ForEach({
    begin {
        $i = 0
        $out = [ordered]@{}
    }
    process {
        $out[$columns[$i++]] = $_
        if($i -eq $columns.count)
        {
            [pscustomobject]$out
            $out = [ordered]@{}
            $i = 0
        }
    }
})

$newObject | ConvertTo-Json

Example of $newObject | ConvertTo-Json:

[
  {
    "TimeGenerated": "2022-01-10T09:25:41.837Z",
    "Computer": "Mycomputer",
    "EventID": 2974,
    "EventLevelName": "Error",
    "RenderedDescription": "The attribute value provided is...."
  },
  {
    "TimeGenerated": "2022-02-10T09:25:41.837Z",
    "Computer": "Mycomputer2",
    "EventID": 1234,
    "EventLevelName": "Error2TestTest",
    "RenderedDescription": "The attribute value provided is...."
  }
]

edited Jan 10, 2022 at 14:38

answered Jan 10, 2022 at 14:15

Santiago Squarzon

65.9k5 gold badges26 silver badges60 bronze badges

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

1 Comment

avinash Over a year ago

Thanks a lot mate..its worked as expected

Collectives™ on Stack Overflow

converting Json table using PowerShell

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