Add key value pair to json file with powershell

Question

I've a json like this, I want to add a line key:value for each line in my file.

For example add a comma and the value KC-NA-asplan-PROJECTS-DEV-01 before the curly bracket.

 [
     {
       "name": "/subscriptions/uifaghy78923/resourceGroups/KC-NA-RSGP-PROJECTS-DEV-01/providers/Microsoft.Web/serverfarms/KC-NA-asplan-PROJECTS-DEV-01"
     },
     {
       "name": "/subscriptions/uifaghy78923/resourceGroups/KC-NA-RSGP-PROJECTS-DEV-01/providers/Microsoft.Web/serverfarms/KC-NA-asplan-PROJECTS-DEV-02"
     }
    ]

$file="pathtojson"

$jsonContent = Get-Content $file | ConvertFrom-Json;


$appserviceplan= $jsonContent.name;

foreach ($app in $appserviceplan) {
$app.Split('/')[8]
}

I'd like to add the corrisponding $app.Split('/')[8] in every line in order to have

     "name": "/subscriptions/uifaghy78923/resourceGroups/KC-NA-RSGP-PROJECTS-DEV-01/providers/Microsoft.Web/serverfarms/KC-NA-asplan-PROJECTS-DEV-01", "value": "KC-NA-asplan-PROJECTS-DEV-01"
 },

Glenn · Answer 1 · 2020-01-09T17:14:59.810

0

Using ConvertTo-Json should work for you:

(Get-Content $file | ConvertFrom-Json) | 
    Select-Object -Property *, @{name ="value"; expression={($_.name -split '/')[8]}} | 
    ConvertTo-Json |
    Out-File -FilePath .\newdata.json

This takes all of the existing properties from each object (originally just name) and adds a new property called value with a value of ($_.name -split '/')[8].

It then converts the resulting objects back to JSON using ConvertTo-Json which, for your sample data, ends up as:

[
    {
        "name":  "/subscriptions/uifaghy78923/resourceGroups/KC-NA-RSGP-PROJECTS-DEV-01/providers/Microsoft.Web/serverfarms/KC-NA-asplan-PROJECTS-DEV-01",
        "value":  "KC-NA-asplan-PROJECTS-DEV-01"
    },
    {
        "name":  "/subscriptions/uifaghy78923/resourceGroups/KC-NA-RSGP-PROJECTS-DEV-01/providers/Microsoft.Web/serverfarms/KC-NA-asplan-PROJECTS-DEV-02",
        "value":  "KC-NA-asplan-PROJECTS-DEV-02"
    }
]

Also note that wrapping (Get-Content $file | ConvertFrom-Json) in parentheses may be required as described in this excellent SO answer.

edited Jan 09 '20 at 17:14

answered Jan 08 '20 at 22:57

Glenn

1,687
15
21

The script run, add the filed in the file, but I get this error and the value for each line is the same. Select-Object : The property cannot be processed because the property "value" already exists. Select-Object -Property *, @{name ="value"; expression={($_.name + CategoryInfo :InvalidOperation: (@{name=/subscri...PROJECT-DEV-02}:PSObject) [Select-Object], PSArgumentException+ FullyQualifiedErrorId : AlreadyExistingUserSpecifiedPropertyNoExpand,Microsoft.PowerShell.Commands.SelectObjectCommand. I want to add for each line the corrisponding $_.name -split '/')[8]} in the json file. – Cyber.Drunk Jan 09 '20 at 08:24
That would happen if your input file already had “value” as a property. Is that the case? – Glenn Jan 09 '20 at 11:26
No, there wasn't. – Cyber.Drunk Jan 09 '20 at 16:28
Is this still the original content of your file? ` [ { "name": "/subscriptions/uifaghy78923/resourceGroups/KC-NA-RSGP-PROJECTS-DEV-01/providers/Microsoft.Web/serverfarms/KC-NA-asplan-PROJECTS-DEV-01" }, { "name": "/subscriptions/uifaghy78923/resourceGroups/KC-NA-RSGP-PROJECTS-DEV-01/providers/Microsoft.Web/serverfarms/KC-NA-asplan-PROJECTS-DEV-02" } ] ` – Glenn Jan 09 '20 at 17:16

score 0 · Accepted Answer · answered Jan 09 '20 at 09:52

Since your Json is an array of objects, I think the obvious way is to iterate through this list of objects and for each of them output a new object including the value property. The easiest way is to use Select-Object.

Something like this:

$file = "D:\blah.json"
$json = Get-Content $file -Raw | ConvertFrom-Json | ForEach-Object {
    $_ | Select-Object name, @{Name = 'value'; Expression = {($_.name -split '/')[8]}}
} | ConvertTo-Json

$json

Result:

[
    {
        "name":  "/subscriptions/uifaghy78923/resourceGroups/KC-NA-RSGP-PROJECTS-DEV-01/providers/Microsoft.Web/serverfarms/KC-NA-asplan-PROJECTS-DEV-01",
        "value":  "KC-NA-asplan-PROJECTS-DEV-01"
    },
    {
        "name":  "/subscriptions/uifaghy78923/resourceGroups/KC-NA-RSGP-PROJECTS-DEV-01/providers/Microsoft.Web/serverfarms/KC-NA-asplan-PROJECTS-DEV-02",
        "value":  "KC-NA-asplan-PROJECTS-DEV-02"
    }
]

Add key value pair to json file with powershell

2 Answers2