How to access a nested object (not array) in JSON using PowerShell

Question

I have a Azure Resource template. Using a PowerShell script task in DevOps for CI/CD, I want to replace a few parameters. This is relatively easy when there is only one array in my nested object. However, once more than one array exists, I get the error: The property 'referenceName' cannot be found on this object. Verify that the property exists and can be set.

Here is a sample of the JSON template file:

{
	"resources": [
		{
			"name": "[concat(parameters('workspaceName'), '/myPipeline')]",
			"type": "Microsoft.Synapse/workspaces/pipelines",
			"apiVersion": "2019-06-01-preview",
			"properties": {
				"activities": [
					{
						"name": "Execute myWarehouse Package",
						"type": "ExecuteSSISPackage",
						"dependsOn": [],
						"policy": {
							"timeout": "0.12:00:00",
							"retry": 0,
							"retryIntervalInSeconds": 30,
							"secureOutput": false,
							"secureInput": false
						},
						"userProperties": [],
						"typeProperties": {
							"packageLocation": {
								"packagePath": "myPackagePath.dtsx",
								"type": "SSISDB"
							},
							"environmentPath": null,
							"connectVia": {
								"referenceName": "SSIS-Integration-Runtime", // CHANGE THIS
								"type": "IntegrationRuntimeReference"
							},
							"loggingLevel": "Basic",
							"projectConnectionManagers": {},
							"packageConnectionManagers": {}
						}
					},
                    {
						"name": "Success Email Report",
						"type": "ExecutePipeline",
						"dependsOn": [
							{
								"activity": "Failure Email",
								"dependencyConditions": [
									"Succeeded"
								]
							}
						],
						"userProperties": [],
						"typeProperties": {
							"pipeline": {
								"referenceName": "DW_Email_Report",
								"type": "PipelineReference"
							},
							"waitOnCompletion": true,
							"parameters": {}
						}
					},
				],
				"policy": {
					"elapsedTimeMetric": {},
					"cancelAfter": {}
				},
				"folder": {
					"name": "SSIS Lift and Shift"
				},
				"annotations": []
			},
			"dependsOn": [
				"[concat(variables('workspaceId'), '/pipelines/DW_Email_Report')]",
				"[concat(variables('workspaceId'), '/pipelines/DW_Failure_Email')]",
				"[concat(variables('workspaceId'), '/integrationRuntimes/SSIS-Integration-Runtime')]" // CHANGE THIS
			]
		},
        {
			"name": "[concat(parameters('workspaceName'), '/SSIS-Integration-Runtime')]", // I DON'T WANT TO CHANGE THIS!
			"type": "Microsoft.Synapse/workspaces/integrationRuntimes",
			"apiVersion": "2019-06-01-preview",
			"properties": {
				"type": "Managed",
				"typeProperties": {
					"computeProperties": {
						"location": "West US 3",
						"nodeSize": "Standard_D2_v2",
						"numberOfNodes": 1,
						"maxParallelExecutionsPerNode": 2,
						"vNetProperties": {
							"vNetId": "/subscriptions/123456/resourceGroups/myResourceGroup/providers/Microsoft.Network/virtualNetworks/vnet-myvnet",
							"subnet": "MY-SYNAPSE-SUBNET-2"
						}
					},
					"ssisProperties": {
						"catalogInfo": {
							"catalogServerEndpoint": "endpoint1",
							"catalogAdminUserName": "sqladmin1234",
							"catalogAdminPassword": {
								"type": "SecureString",
								"value": "**********"
							},
							"catalogPricingTier": null
						},
						"edition": "Standard",
						"licenseType": "BasePrice"
					}
				}
			},
			"dependsOn": []
		},
	]
}

And here is a copy of the script I'm trying to run

$jsonFilePath = "C:\Users\me\Downloads\test_template.json"
$jsonData = Get-Content $jsonFilePath -Raw | ConvertFrom-Json

$jsondata.Resources |
ForEach-Object {
    if ($_.name -eq "[concat(parameters('workspaceName'), '/myPipeline')]") {
        $_.properties.activities.typeProperties.connectVia.referenceName =
        $_.properties.activities.typeProperties.connectVia.referenceName.Replace("SSIS-Integration-Runtime", "Azure-SSIS-IntegrationRuntime-PROD")
    }
}

Set-Content ($jsonData | ConvertTo-Json -Depth 100) -Path "C:\Users\me\Downloads\test_template2.json"

I've also tried this route, calling the nested object and piping the output inside the if statement

$jsonFilePath = "C:\Users\me\Downloads\test_template.json"
$jsonData = Get-Content $jsonFilePath -Raw | ConvertFrom-Json

$jsondata.Resources |
ForEach-Object {
    if ($_.name -eq "[concat(parameters('workspaceName'), '/myPipeline')]") {
        $_.properties.activities |
        Select-Object $_.typeProperties.connectVia.referenceName =
        $_.properties.activities.typeProperties.connectVia.referenceName.Replace("SSIS-Integration-Runtime", "Azure-SSIS-IntegrationRuntime-PROD")
    }
}

Set-Content ($jsonData | ConvertTo-Json -Depth 100) -Path "C:\Users\me\Downloads\test_template2.json"

But this gives a different error: A positional parameter cannot be found that accepts argument '='.

Any help is appreciated!

Answer 1

It would be appreciated if you'd verify that the JSON you post passes the sniff test. 1) JSON has no comments, 2) there were two trailing commas, one on line 56 and another on line 106.

Try using "JSON Lint": https://jsonlint.com/

Your JSON data isn't orthogonal. Not every sub section has the same keys. See if this handles your problem.

$jsonFilePath = "C:\Users\me\Downloads\test_template.json"
$jsonData = Get-Content $jsonFilePath -Raw | ConvertFrom-Json

$jsondata.Resources |
    ForEach-Object {
        if ($_.name -eq "[concat(parameters('workspaceName'), '/myPipeline')]") {
            foreach ($activity in ($_.properties.activities)){          # use iteration in case of multiple activities
                if ($activity.typeProperties.connectVia){               # not every typeProperties has a connectVia
                    $activity.typeProperties.connectVia.referenceName =
                        $activity.typeProperties.connectVia.referenceName.Replace("SSIS-Integration-Runtime", "Azure-SSIS-IntegrationRuntime-PROD")
                }
            }
        }
    }

    Set-Content ($jsonData | ConvertTo-Json -Depth 100) -Path "C:\Users\me\Downloads\test_template2.json"