Update JSON file using PowerShell
Solution 1
Your json is missing the starting and ending curly brackets:
{
"policies": {
"Framework.DataContext": {
"connectionString": "Server=ServerName;Database=DateBaseName;Integrated Security=sspi;"
}
}
}
Now you can update the file like this:
$pathToJson = "F:\Path\To\JSON\file.json"
$a = Get-Content $pathToJson | ConvertFrom-Json
$a.policies.'Framework.DataContext'.connectionString = "Server=ServerName;Database=DateBaseName;Integrated Security=sspi2;"
$a | ConvertTo-Json | set-content $pathToJson
You could also use some Select-Object
to get the property:
$connectionString = $a | select -expand policies | select -expand Framework.DataContext
$connectionString.connectionString = 'Test'
Solution 2
$s = Get-Content "F:\Path\To\JSON\file.json" -Raw|ConvertFrom-Json
$s.policies.'Framework.DataContext'.connectionString="Server=ServerName;Database=DateBaseName;Integrated Security=sspi2;"
$s|ConvertTo-Json |Set-Content "F:\Path\To\JSON\file.json"
Solution 3
I also faced the similar problem. Fixed it by specifying the INDEX of the object I was trying to edit.
$a.policies[0].'Framework.DataContext'.connectionString = "Server=ServerName;Database=DateBaseName;Integrated Security=sspi;"
Hope it helps!
Related videos on Youtube
pstricker
Updated on July 09, 2022Comments
-
pstricker almost 2 years
I am currently trying to setup a continuous integration system using VSTS and have run into a bit of a snag. As part of the release process I need to update a specific object value in a JSON file depending on the environment. The only tools it seems I have at my disposal that might get this done in the VSTS environment is PowerShell.
I've done quite a bit of research and have not been able to figure out how exactly this can be done. I found this question and answer here on Stack Overflow "How do I update JSON file using PowerShell" but executing the script provided in the answer changes the structure of the JSON file substantially and adds quite a bit of what looks like PowerShell metadata.
Ideally, I would like to take an existing JSON file that gets deployed and update the value of the connectionString property in the example JSON below.
{ "policies": { "Framework.DataContext": { "connectionString": "Server=ServerName;Database=DateBaseName;Integrated Security=sspi;" } } }
Does anyone have any advice on how to accomplish this? So far I have tried running the following script but it throws an "The property 'connectionString' cannot be found on this object. Verify that the property exists and can be set." exception. I have verified that the object traversal is correct and the connectionString property exists.
$pathToJson = "D:\Path\To\JSON\file.json" $a = Get-Content $pathToJson | ConvertFrom-Json $a.policies.'Framework.DataContext'.connectionString = "Server=ServerName;Database=DateBaseName;Integrated Security=sspi;" $a | ConvertTo-Json | set-content $pathToJson
The full contents of file.json are as follows
{ "log": { "level": 0, "file": "c:\\temp\\simport.log", "formats": { "error": null, "start": null, "requestBegin": null, "requestWork": "", "requestError": null, "requestEnd": null, "stop": null }, "eventLog": { "name": "Application" } }, "diagnostic": { "stackTrace": false }, "api": { "simport": true }, "roles": { "0": "Anonymous", "1": "Administrator", "2": "Participant", "3": "Facilitator" }, "pathType": { "area": 1, "region": 2, "session": 3, "team": 4 }, "scenarios": { "default": { "default": true, "initState": "Init", "rounds": [ { "name": "round1", "displayName": "R1", "beginTime": 1, "endTime": 3 }, { "name": "round2", "displayName": "R2", "beginTime": 4, "endTime": 6 }, { "name": "round3", "displayName": "R3", "beginTime": 7, "endTime": 9 }, { "name": "round4", "displayName": "R4", "beginTime": 10, "endTime": 12 } ] } }, "simportQueries": { "package": "bin/trc.simport3.zip" }, "customQueries": { "package": "app/config/custom-queries.zip", "parameters": { } }, "audit": { "Path.Create": true, "Path.Delete": true, "Team.Create": true, "Team.Update": true, "Team.Delete": true, "SimportData.SaveValues": true }, "tasks": { "task1": { "state": "", "required": "", "completed": "C:Task1Status:+0" } }, "feedback": { "welcome": { "text": { "": "en-us", "en-us": "Welcome" } } }, "contentCategories": { "demo1": { "round": 1 } }, "policies": { "Simport.Web.Module": { "fileMask": ".aspx,.asmx", "deny": { "statusCode": 404, "statusDescription": "Not found", "location": [ "/{0,1}app/config/(.*\\.json)$", "/{0,1}app/config/(.*\\.xml)$", "/{0,1}app/config/(.*\\.zip)$", "/{0,1}app/config/(.*\\.xlsx)$" ] }, "formDataContentType": [ "application/x-www-form-urlencoded" ] }, "Framework.DataContext": { "connectionString": "Server=(local);Database=Simport3;Integrated Security=sspi;", "commandTimeout": 30 }, "Simport.Security": { "passwordEncryption": "", "passwordSalt": "", "passwordPolicy": { "disabled": true, "min": 8, "max": 100, "rules": [ { "id": "digit", "pattern": "\\d+", "flags": "i" }, { "id": "letter", "pattern": "\\w+", "flags": "i" }, { "id": "upper", "pattern": "[A-Z]+" }, { "id": "lower", "pattern": "[a-z]+" }, { "id": "special", "pattern": "[\\!#@\\$_~]+", "flags": "i" }, { "id": "prohibited", "pattern": "[\\\\/'\"\\?\\^&\\+\\-\\*\\%\\:;,\\.]+", "flags": "gi", "match": false } ] } }, "Simport.PackageDefinition": { "path": "~/app/config/manifest.xml" }, "Security.SignIn": { "result": { "default": "u,p,p.props,t" }, "claims": [ [ "userId", "firstName", "lastName" ] ] }, "Security.GetContext": { "result": { "default": "u,p,p.props,pr,t" } }, "Security.ChangePassword": { "allowedRoles": [ 1, 2, 3 ] }, "Security.ResetPassword": { "allowedRoles": [ 1, 2 ] }, "Security.Register": { "allowedRoles": [ 0 ], "!pathType-0": 4, "!roleId-0": 2 }, "Path.Create": { "allowedRoles": [ 1, 2, 3 ] }, "Path.Select": { "allowedRoles": [ 1, 2, 3 ], "result-1": { } }, "Path.Delete": { "allowedRoles": [ 1, 2 ] }, "User.Select": { "allowedRoles": [ 1, 2, 3 ], "result": { "select": [ "id", "pathid", "roleid", "name", "email", "login", "props" ], "restrict": [ "password" ] }, "result-1": { "select": "*", "group": true } }, "User.Create": { "allowedRoles": [ 1, 2 ], "result": { "select": [ "id", "pathid", "roleid", "name", "email", "login", "props" ], "restrict": [ "password" ] }, "result-1": { "select": "*", "group": true } }, "User.Update": { "allowedRoles": [ 1, 2, 3 ], "result": { "select": [ "id", "pathid", "roleid", "name", "email", "login", "props" ], "restrict": [ "password" ] } }, "User.Delete": { "allowedRoles": [ 1, 2 ], "result": { "restrict": [ "password" ] } }, "Session.Select": { "allowedRoles": [ 1, 2, 3 ], "enforcePathLevel": true, "result": { "default": [ "name", "beginDate", "endDate" ], "restrict": [ "password" ] }, "result-1": { "default": [ "name", "beginDate", "endDate" ], "treeAllowed": true, "treeDefault": false } }, "Session.Create": { "allowedRoles": [ 1, 2 ], "enforcePathLevel": true }, "Session.Update": { "allowedRoles": [ 1, 2 ], "enforcePathLevel": true, "update-restictions": [ "password" ], "update-restictions-1": [ ], "result": { "restrict": [ "password" ] } }, "Session.Delete": { "allowedRoles": [ 1, 2 ], "result": { "restrict": [ "password" ] } }, "Team.Select": { "allowedRoles": [ 1, 2, 3 ], "enforcePathLevel": false, "enforcePathLevel-1": true, "result-1": { "treeAllowed": true, "treeDefault": false } }, "Team.Create": { "allowedRoles": [ 1, 2, 3 ], "enforcePathLevel": true, "enforcePathLevel-1": false, "allowMultiple": false, "allowMultiple-1": true, "result": { }, "overrides": { "roleID": 3 } }, "Team.Reset": { "allowedRoles": [ 1, 2, 3 ] }, "Team.Delete": { "allowedRoles": [ 1, 2, 3 ], "deleteMultiple": true, "result": { "default": "t" } }, "Team.TransitionTo": { "allowedRoles": [ 1, 2, 3 ], "inboundRules": { "Round1Init": { "allowedRoles": [ ] } }, "outboundRules": { "Round1Wait": { "allowedRoles": [ 1, 2, 3 ] } } }, "Team.TakeControl": { "allowedRoles": [ 1, 2, 3, 4 ], "select-1": { "select": "*", "restrict": [ "ctrl.userID", "ctrl.loginName" ] } }, "Team.ReleaseControl": { "allowedRoles": [ 1, 2, 3, 4 ], "select-1": { "select": "*", "restrict": [ "ctrl.userID", "ctrl.loginName" ] } }, "Team.GetStatus": { "allowedRoles": [ 1, 2, 3 ], "result": { "default": "p,t,pr" } }, "Data.Select": { "allowedRoles": [ 1, 2, 3 ] }, "Data.Update": { "allowedRoles": [ 1, 2, 3 ], "audit": { "g": false, "i": true, "o": true, "s": true } }, "Data.ExecuteQuery": { "allowedRoles": [ 0, 1, 2, 3 ], "allowed-0": [ "login4\\areas", "login4\\regions", "login4\\sessions", "login4\\teams" ], "restrict-3": [ "prohibitedQueryNameHere" ] }, "Document.Select": { "defaultTextEncoding": "utf-16" }, "Document.Create": { "~allowFileExt": [ ], "denyFileExt": [ ".exe", ".com", ".cmd", ".bat", ".ps1" ], "~allowContentType": [ ], "denyContentType": [ "application/x-msdownload" ], "maxContentLength": 0, "defaultTextEncoding": "utf-16" }, "Document.Update": { "allowedRoles": [ 1, 2, 3 ] }, "Document.Delete": { }, "Document.Download": { } } }
-
pstricker almost 8 yearsThanks for your answer. I've updated to reflect the curly braces and the exception I am now receiving.
-
Martin Brandl almost 8 yearsIf i store the json and change the path to it, the scripts runs as expected. What PowerShell version are you using? Did you changed anything else?
-
pstricker almost 8 yearsI am running the following PowerShell 5
-
Martin Brandl almost 8 yearsDid you changed anything else? this is working without any error here. But I can provide you another example using selects, give me a min
-
Martin Brandl almost 8 yearsOkay, I updated my answer. Just replace the line
$a.policies.....
with the two lines I added -
pstricker almost 8 yearsGetting close. Now throwing the following: select : Property "policies" cannot be found. At line:3 char:26 + $connectionString = $a | select -expand policies | select -expand Fra ... + ~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidArgument: (@{value=}; PSPa... ReadCount=393}:PSObject) [Select-Object], PSArgumentException + FullyQualifiedErrorId : ExpandPropertyNotFound,Microsoft.PowerShell.Commands.SelectObjectCommand
-
Martin Brandl almost 8 yearsNo, same problem. what output do you get if you write
$a.GetType()
after$a = Get-Content $pathToJson | ConvertFrom-Json
-
pstricker almost 8 yearsObject[] with a BaseType of System.Array
-
Martin Brandl almost 8 yearsThis is wrong, It should be a
PsCustomObject
. Try to add parenthesis :$a = (Get-Content $pathToJson | ConvertFrom-Json)
. Also, try to copy my example json and don't change anything and try if this work. -
TravisEz13 almost 8 yearsI also tried @jisaak 's example and it works without issue on PowerShell 5
-
pstricker almost 8 yearsCopying and pasting the object worked so something must be wrong with my JSON file. Adding actual JSON file contents to original question.
-
pstricker almost 8 yearsTurns out my JSON was not valid as it had a empty property name in the feedback object. Given that property a name solved the issue. Thanks for your help!
-
Mike Asdf over 6 yearsRelated tip for fellow googlers:
Add-Member
is useful if the property does not already exist.