CODEWITHSUNDEEP
arraysjsonpowershell
Thomas
Thomas

Reputation: 4297

Powershell: Reformat JSON nested Arrays to Array of Objects

I'm trying to modify a badly formated JSON Textfile, that currently looks like this:

[
    ["val1", "val2", "val3", "val4"],
    ["val5", "val6", "val7", "val8"],
    ["val9", "val10", "val11", "val12"]
]

and I have an other array containing field names

["title1", "title2", "title3", "title4"]

I want to output a final.json textfile looking like this:

[
    {"title1": "val1", "title2": "val2", "title3": "val3", "title4": "val4"}, 
    {"title1": "val5", "title2": "val6", "title3": "val7", "title4": "val8"},
    {"title1": "val9", "title2": "val10", "title3": "val11", "title4": "val12"}
]

I guess the best way would be to take each row, split by , and then adding them back together foreach-ing over the title names, but I'm not quite sure on how to do this in PowerShell.

Upvotes: 4

Views: 3209

Answers (2)

Keith Hill
Keith Hill

Reputation: 201592

I think a better approach is to read in the first JSON format via ConvertFrom-Json, then take that array of arrays and for each row, create a PSCustomObject from a hashtable e.g. [PSCustomObject]@{title1=$arr[$row][0]; title2=$arr[$row][1];...}. Once you then have that array of PSCustomObject, convert that back to JSON with ConvertTo-Json.

Upvotes: 2

briantist
briantist

Reputation: 47772

Since you're dealing with structured data here, I think the best way is to parse the JSON, and work with the resulting objects. Create the objects you want, then convert back to JSON:

$j1 = @'
[
    ["val1", "val2", "val3", "val4"],
    ["val5", "val6", "val7", "val8"],
    ["val9", "val10", "val11", "val12"]
]
'@

$j2 = @'
["title1", "title2", "title3", "title4"]
'@

$a1 = $j1 | ConvertFrom-Json
$a2 = $j2 | ConvertFrom-Json

0..($a1.Count-1) | ForEach-Object {
    $i = $_
    $props = @{}
    0..($a2.Count-1) | ForEach-Object {
        $props[$a2[$_]] = $a1[$i][$_]
    }
    New-Object PSOBject -Property $props
} | ConvertTo-Json

ConvertTo-Json and ConvertFrom-Json are the cmdlets you need to serialize/deserialize the JSON. Then you just work with the objects.

In this case I'm going through each top level array in $a1 and creating a hashtable that contains the properties you want. Then I'm creating a PSObject with those properties. That gets returned from the ForEach-Object cmdlet (the result is an array of those objects) which then gets piped directly into ConvertTo-Json to give you the output needed.

Upvotes: 3

Related Questions