Compare and update Json Arrays

Question

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.

Welcome to SO, can you please share the error you are getting as well. — DevX
– DevX, Commented Mar 2, 2020 at 6:12
Forgot to say - you don't need the Out-String in the Get-Content pipeline. Nor the brackets. $json2 = Get-Content "C:\Users\parameters2.json" -Raw | ConvertFrom-Json — LeeM
– LeeM, Commented Mar 2, 2020 at 8:48

Dave · Accepted Answer · 2020-03-05 16:36:23Z

2

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
    }
}

edited Mar 5, 2020 at 16:36

answered Mar 2, 2020 at 16:32

Dave

633 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

LeeM Over a year ago

d'oh, this is much better than fumbling thru the entire 2nd object and is literally 20x faster than my suggestion

Tamanna Agarwal Over a year ago

Thankyou, that worked!. There something more that I would help with. just like CIT_Properties, there can be another section as script_properties which may or may not exist but when it does exist, same operation as CIT_properties needs to be performed. Is there a way to not restrict the code to CIT_properties?

Dave Over a year ago

Rough example added to my answer it assumes that if script_properties exists, it exists in both json files. I have also left the property names as Property and Value.

Tamanna Agarwal Over a year ago

I see what you did there.but Script_properties may or may not exist in j2 is the idea. I can replace the check from j1 to j2 and continue. However, when I do so, I could see that in a case where both the files have 2 properties each under script properties, because it is not able to find any match it throws error for those indexes. This is not the case when J1 has no script properties section but j2 has script properties. Also, because it is the same operation, can we not use multiple conditions in foreach loop to accommodate both conditions?

Dave Over a year ago

I think my latest edit will do what you want. There are two arrays, one of which may not exist, it is best to handle each with it's own loop.

|

LeeM · Accepted Answer · 2020-03-02 23:54:46Z

1

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)

edited Mar 2, 2020 at 23:54

answered Mar 2, 2020 at 8:36

LeeM

1,26811 silver badges18 bronze badges

1 Comment

LeeM Over a year ago

I was coming down with a cold - my excuse for the dumb answer

Collectives™ on Stack Overflow

Compare and update Json Arrays

2 Answers 2

9 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related