Compare and update Json Arrays

user_12929735 0
Viewed 327

I have two sets of json file: Parameters1.json and parameters2.json as seen below:

Parameters1.json

{
    "TypeofService":"CITService",
    "ServiceName":"abc",
    "SCNNAME":"abc_V1.scn",
    "ScheduleInterval":"Daily",
    "ScheduleDay":"MON,TUE,WED,THU,FRI,SAT",
    "ScheduleTime":"08:30",
    "Folder_structure": "Success\\test1,Success\\test2",
    "CIT_Properties":  [
                           {
                               "isPassword":  false,
                               "Property":  "host1",
                               "Value":  "xyz"
                           },
                           {
                               "isPassword":  false,
                               "Property":  "Port1",
                               "Value":  "8081"
                           },
                           {
                               "isPassword":  false,
                               "Property":  "user1",
                               "Value":  "testuser"
                           },
                           {
                               "isPassword":  true,
                               "Property":  "password1",
                               "Value":  "12345"
                           }
                       ]
}

Parameters2.json(file to be updated)

{
    "TypeofService":"CITService",
    "ServiceName":"abc",
    "SCNNAME":"abc_V2.scn",
    "ScheduleInterval":"Daily",
    "ScheduleDay":"MON,TUE,WED,THU,FRI,SAT",
    "ScheduleTime":"08:30",
    "Folder_structure": "Success\\test1,Success\\test2",
    "CIT_Properties":  [
                           {
                               "isPassword":  false,
                               "Property":  "host1",
                               "Value":  "xyz"
                           },
                           {
                               "isPassword":  false,
                               "Property":  "port1",
                               "Value":  "8080"
                           },
                           {
                               "isPassword":  false,
                               "Property":  "user1",
                               "Value":  "generic"
                           },
                           {
                               "isPassword":  true,
                               "Property":  "password1",
                               "Value":  "56789"
                           },
                           {
                               "isPassword":  false,
                               "Property":  "host2",
                               "Value":  "xyz2"
                           },
                           {
                               "isPassword":  false,
                               "Property":  "port2",
                               "Value":  "8080"
                           },
                           {
                               "isPassword":  false,
                               "Property":  "user2",
                               "Value":  "user2"
                           },
                           {
                               "isPassword":  true,
                               "Property":  "password2",
                               "Value":  "1234567890"
                           }
                       ]
}

What I am trying to achieve is that if the 'Property' in CIT_Properties matches in both the files, then the corresponding CIT_properties.value from parameters1.json should be updated in parameters2.json.

i.e if you see above: port1, user1 and password1 are common in both the files. I would like the values (8080,generic,56789 in parameters2.json) to be replaced with (8081,testuser,12345).

What I have done so far is below:

$json1 = (Get-Content "C:\Users\parameters1.json" -Raw) | Out-String | ConvertFrom-Json
$json2=(Get-Content "C:\Users\parameters2.json" -Raw) | Out-String | ConvertFrom-Json

for ($a = 0; $a -lt $json2.PsObject.properties.value.Property.length; $a++)
{
    for ($b = 0; $b -lt $json1.PsObject.properties.value.Property.length; $b++)
    {
        if ($json2.PsObject.properties.value.Property[$a] -eq $json1.PsObject.properties.value.Property[$b])
    {
            $json2.PsObject.properties.value.Value[$a]=  $json1.PsObject.properties.value.Value[$b]
        }
    }
}

I see that I am able to access the individual values but when I try setting them, it throws me an error.

object powershell json
2 Answers

Where-Object is your friend here. If you loop through the smaller array in file 1 it will be more efficient. There is no need to have a nested loop as Where Object should work quicker. This was tested in Powershell Core but should work in all versions. You can use Set-Content to update you json file or create a new file from the variable $j2. 

$j1 = Get-Content ./parameters1.json -Raw | ConvertFrom-Json
$j2 = Get-Content ./parameters2.json -Raw | ConvertFrom-Json

foreach ($Obj in $j1.CIT_Properties)
{
    ($j2.CIT_Properties | where {$_.Property -eq $Obj.Property}).Value = $Obj.Value
}

EDIT: Added example to answer comment. This answer assumes that if script_properties exists in j1 it also exists in j2, I can expand the answer if you update the json in your question.

existing in j2.

$j1 = Get-Content ./J1.json -Raw | ConvertFrom-Json
$j2 = Get-Content ./J2.json -Raw | ConvertFrom-Json

foreach ($Obj in $j1.CIT_Properties)
{
    ($j2.CIT_Properties | where {$_.Property -eq $Obj.Property}).Value = $Obj.Value
}

if ($j1.script_properties)
{
    if($j2.script_properties)
    {          
        foreach ($Obj in $j1.CIT_Properties)
        {
            ($j2.script_properties | where {$_.Property -eq $Obj.Property}).Value = $Obj.Value
        }
    }
    else 
    {
        $j2 | Add-Member -MemberType NoteProperty -Name script_properties -Value $j1.script_properties
    }
}

EDIT: I think this is what you are looking for. I added a condition to check if the j2 array contains $obj

$j1 = Get-Content ./J1.json -Raw | ConvertFrom-Json
$j2 = Get-Content ./J2.json -Raw | ConvertFrom-Json

foreach ($Obj in $j1.CIT_Properties)
{
    if ($j2.CIT_Properties -contains $obj)
    {
        ($j2.CIT_Properties | where {$_.Property -eq $Obj.Property}).Value = $Obj.Value
    }
}

if ($j1.script_properties)
{
    if($j2.script_properties)
    {          
        foreach ($Obj in $j1.CIT_Properties)
        {
            if ($j2.script_Properties -contains $obj)
            {
                ($j2.script_properties | where {$_.Property -eq $Obj.Property}).Value = $Obj.Value
            }
        }
    }
    else 
    {
        $j2 | Add-Member -MemberType NoteProperty -Name script_properties -Value $j1.script_properties
    }
}
user_126691750

Edit: just use @Dave's answer

This isn't super elegant and is probably a bit slow if your datasets are large.

But what's complicating things is that your $json[x].CIT_Properties are actually arrays of child PSCustomobjects. You can see this by doing $json1.Cit_properties.gettype(), which shows it's an array.

So all I could think to do was iterate through both sets of CIT_Properties and update the value attribute where the property values are the same

ForEach ($o in $json1.CIT_Properties) {
    ForEach ($t in $json2.CIT_Properties) {
     if ($t.Property -eq $o.Property) {
            $t.Value = $o.Value
        }
    }
}

(btw, I simply named the loop variables $o for "one" and $t for "two" to make it crystal clear which json source you're indexing)

user_31732710
Related