Appending to an array in json file using powershell script is giving error

Question

 {
    "$schema": "a",
    "contentVersion": "b",
    "extensions": [   
    {
      "Name": "c",
      "Type": "d",
      "Version": "e",
      "ConnectionProperties": {
        "MaxExecutionTime": "f",
        "Authentication": {
          "Type": "g",
          "Reference": {
            "Provider": "h",
            "Parameters": {
              "SecretId": "i"
            }
          },
          "Properties": {
            "ApplicationId": "j",
            "TenantId": "k"
          }
        }
      },
      "payloadProperties": {
        "ConnectionString": {
          "Value": "l"
        },
        "KqlSasUri": {
          "reference": {
            "path": "m",
            "enableScopeTagBindings": "n"
          }
        },        
        "DatabaseName": {
          "Value": "o"
        }
      },
      "repeatOver": [
        {
          "name": "p",
          "file": "q",
          "database": "r"
        }        
      ]
    }
  ]
}

Above is the json file, I wish to append to repeatOver array using powershell script

$getContentfromDestinationJson = Get-Content $pathToDestinationJson -Raw | ConvertFrom-Json

$destinationExtension = $getContentfromDestinationJson.extensions

Write-Host $destinationExtension

Output is --

@{Name=a; Type=b; Version=c; ConnectionProperties=; payloadProperties=; repeatOver=System.Object[]}

Also, when I try to write append to this repeatOver array in this json.

Note- the below code is in a for loop, I want to append this multiple times to the array..

 for ($i=0; $i -le 3; $i++) {    

   $toWrite= [PSCustomObject]@{        
            name     = 'd'
            file     =  'e'
            database = 'f'  
        }

   $destinationExtension.repeatOver += (ConvertTo-Json -InputObject $toWrite -Depth 3)

 }

I see this error :

  Method invocation failed because [System.Management.Automation.PSObject] does not contain a method named 'op_Addition'.

Also, how do I skip adding a $toWrite value if it already exists, by comparing the name field?

Answer 1

You would need to access the psobject Properties to update that property, I would love to see if there is an easier way of doing this.

Assuming I have the JSON stored in the $json variable, if I try to update .extensions.repeatOver:

PS /> $json.extensions.repeatOver

name file database
---- ---- --------
p    q    r       

PS /> $json.extensions.repeatOver = @()
The property 'repeatOver' cannot be found on this object. Verify that the property exists and can be set.
At line:1 char:1
+ $json.extensions.repeatOver = @()
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : PropertyAssignmentException

To update it you can do it like this:

# Here I'm appending the value from repeatOver with random data
$toAppend = @(
    $json.extensions.repeatOver
    for($i=0; $i -le 3; $i++)
    {    
        [PSCustomObject]@{        
            name     = Get-Random -Maximum 9
            file     = Get-Random -Maximum 9
            database = Get-Random -Maximum 9
        }
    }
)

$json.psobject.Properties.where({ $_.Name -eq 'extensions' }).Value.repeatOver = $toAppend

Testing if it works:

PS /> $json.extensions

Name                 : c
Type                 : d
Version              : e
ConnectionProperties : @{MaxExecutionTime=f; Authentication=}
payloadProperties    : @{ConnectionString=; KqlSasUri=; DatabaseName=}
repeatOver           : {@{name=p; file=q; database=r}, @{name=3; file=4; database=2}, @{name=5; file=3; database=6}, @{name=1; file=0; database=7}...}

PS /> $json.extensions.repeatOver

name file database
---- ---- --------
p    q    r       
3    4    2       
5    3    6       
1    0    7       
6    3    8       

---

I think this is also relevant: https://github.com/PowerShell/PowerShell/issues/3159

Answer 2

Since the extensions is also an array, you need to loop over that to append your object array to the repeatOver array.

# read the json file and convert it
$json = Get-Content -Path 'Path\To\The\JSON_file.json' -Raw | ConvertFrom-Json
# create your new object array
$toWrite = for ($i=0; $i -le 3; $i++) {    
    [PSCustomObject]@{        
        name     = "d$i"
        file     = "e$i"
        database = "f$i"  
    }
}
# loop over the extensions array and append to repeatOver 
$json.extensions | ForEach-Object { $_.repeatOver += $toWrite }

$json | ConvertTo-Json -Depth 6 # | Set-Content -Path 'Path\To\The\Extended_JSON_file.json'

^{P.S. PowerShell does not produce 'pretty' json. If you need to convert it to properly spaced json, see my function Format-Json}