0

I am trying to convert JSON array into single JSON Object. Below is the output screenshot enter image description here

I want the output as enter image description here

I am using the below powershell script

$getdb_Conn=Invoke-Sqlcmd -ServerInstance $ServerName -Database 'master' -Username $UserName -Password $Password -Query $getdb $deadlockDB=$getdb_Conn.Database_Name $deadlockSP=$getdb_Conn.SP_Name $deadlockTable_Name=$getdb_Conn.Table_Name $deadlockTIMESTAMP=$getdb_Conn.TIMESTAMP $Obj = [PSCustomObject]@{ Database = $deadlockDB SP = $deadlockSP Table = $deadlockTable_Name TIMESTAMP = $deadlockTIMESTAMP } Write-Output ( $obj | ConvertTo-Json)

Please someone help me, on how to the get required output. I do not want the JSON in an array.

Improve this question

1 Answer 1

Reset to default
2

The issue is you have one single non-array object that contains properties, which all contain arrays of values. To achieve the desired results, you need an array of objects that each contain one set of property values. You can choose to do this at the source when you are building $Obj or build a new set of objects to be sent to ConvertTo-Json.

For building a new set of objects using your current $Obj:

for ($i = 0; $i -lt $obj.TIMESTAMP.Count; $i++) { [pscustomobject]@{ Database = $obj.Database[$i] SP = $obj.SP[$i] Table = $obj.Table[$i] TIMESTAMP = $obj.TIMESTAMP[$i] } | ConvertTo-Json }

Building the objects from the source data:

$getdb_Conn=Invoke-Sqlcmd -ServerInstance $ServerName -Database 'master' -Username $UserName -Password $Password -Query $getdb foreach ($conn in $getdb_Conn) { [PSCustomObject]@{ Database = $conn.Database_Name SP = $conn.SP_Name Table = $conn.Table_Name TIMESTAMP = $conn.TIMESTAMP } | ConvertTo-Json }
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.