How to import this Json data as a flat object rather than a nested object

Question

I have a json file, its content is:

{
    "PID": [
        18988,
        23928,
        16656
    ],
    "Process": [
        "pwsh.exe",
        "pwsh.exe",
        "WindowsTerminal.exe"
    ],
    "WinID": [
        "0x1117b6",
        "0x130042",
        "0x600848"
    ]
}

I want to import it as a PsCustom object, I have tried Get-Content -Path c:\temp\MyJson.json|ConvertFrom-Json but this imports the data as a nested object:

PID                   Process                                   WinID
---                   -------                                   -----
{18988, 23928, 16656} {pwsh.exe, pwsh.exe, WindowsTerminal.exe} {0x1117b6, 0x130042, 0x600848}

I am expecting the following:

PID                   Process                                   WinID
---                   -------                                   -----
18988                       pwsh.exe                                                0x1117b6
23928                       pwsh.exe                                                0x130042
16656                       WindowsTerminal.exe                             0x600848

I have restructured the Json file multiple times, I am not sure where I am going wrong. I am open to changing the json file itself.

I would much rather solve this solution at the json file itself, rather in PowerShell with something like ...ConverFrom-Json| Where-Oject..., can someone share with me the correct json format to provide for PowerShell, in this case?

Any help would be greatly appreciated!

with the function from [this answer](https://stackoverflow.com/a/73640422/15339544) you can do `$json.PSObject.Properties | ForEach-Object { , $_.Value } | Join-Array $json.PSObject.Properties.Name` — Santiago Squarzon, Aug 31 '23 at 18:41

score 1 · Accepted Answer · answered Aug 31 '23 at 21:55

The JSON is imported as a single object with 3 properties: PID, Process, and WinID. Each of those properties contains 3 values. What you're requesting is to have 3 different objects, each with the PID, Process, and WinID properties and containing one of each of those values.

If you control the JSON you're capturing (I assume you do, since you're pulling it from a file), the simplest solution would be to change the way you're structuring the JSON to match what you expect:

[
    {
        "PID": 18988,
        "Process": "pwsh.exe",
        "WinID": "0x1117b6"
    },
    {
        "PID": 23928,
        "Process": "pwsh.exe",
        "WinID": "0x130042"
    },
    {
        "PID": 16656,
        "Process": "WindowsTerminal.exe",
        "WinID": "0x600848"
    }
]

PS > $json | ConvertFrom-Json

  PID Process             WinID
  --- -------             -----
18988 pwsh.exe            0x1117b6
23928 pwsh.exe            0x130042
16656 WindowsTerminal.exe 0x600848

If you can't change the source JSON, this will turn what you have from one object into three:

PS > for ($i = 0 ; $i -lt 3; $i++){
    [PSCustomObject]@{
        PID = $json.PID[$i]
        Process = $json.Process[$i]
        WinID = $json.WinID[$i]
    }
}

  PID Process             WinID
  --- -------             -----
18988 pwsh.exe            0x1117b6
23928 pwsh.exe            0x130042
16656 WindowsTerminal.exe 0x600848

I restructed my json file to follow your example, perfect. Cheers mate. — Ralf_Reddings, Sep 01 '23 at 17:43

score 1 · Answer 2 · answered Sep 01 '23 at 11:08

Using the JoinModule from the PowerShell Gallery, you could dynamically (independently of the column names and sizes) join the properties list side-by-side:

$Object = Get-Content -Path c:\temp\MyJson.json | ConvertFrom-Json
$Transpose = @()
$Object.PSObject.Properties.Name | Foreach-Object {
    $Transpose = $Transpose | FullJoin-Object $Object.$_ -Name $_
}
$Transpose

  PID Process             WinID
  --- -------             -----
18988 pwsh.exe            0x1117b6
23928 pwsh.exe            0x130042
16656 WindowsTerminal.exe 0x600848

How to import this Json data as a flat object rather than a nested object

2 Answers2

Linked