Powershell merge two array based on maaping and generate resulting array

Question

I have below two arrays.

# My First request
$MyResultArray1 = Invoke-RestMethod -Uri "http://myhost/rest-api-1" -Method GET -ErrorAction 'Stop'

# My Second request
$MyResultArray2 = Invoke-RestMethod -Uri "http://myhost/rest-api-2" -Method GET -ErrorAction 'Stop'

Both the request returns the JSON Array something like Below.

#Array 1 - $MyResultArray1
{
  "values": [
    {
       "id": 1,
       "testType": "something1"
    },
    {
       "id": 2,
       "testType": "something2"
    },
    {
       "id": 3,
       "testType": "something3"
    }
  ]
}

#Array 2 - $MyResultArray2
{
  "values": [
    {
       "testId": 1,
       "testResult": "result1",
       "testMeta": {}
    },
    {
       "testId": 2,
       "testResult": "result2",
       "testMeta": {}
    },
    {
       "testId": 3,
       "testResult": "result3",
       "testMeta": {}
    }
  ]
}

I want to merge these JSON array based on the ID and generate the resulting array as below.

#Array 3 - $Result
{
  "result": [
    {
       "id": 1,                  <-- From Array#1
       "testType": "something1"  <-- From Array#1
       "testResult": "result1"   <-- From Array#2
    },
    {
       "id": 2,
       "testType": "something2"
       "testResult": "result2"
    },
    {
       "id": 3,
       "testType": "something3"
       "testResult": "result3"
    },
  ]
}

I want to match the testId with id and generate the resulting array. I want to search for the $MyResultArray2.testId in the $MyResultArray1 and add the new field "testResult" in $MyResultArray1.

I am very new to Powershell, I am not sure how it is done in Powershell.
I able to iterate array but not able to search and create new object.

Using this [`Join-Object script`](https://www.powershellgallery.com/packages/Join)/[`Join-Object Module`](https://www.powershellgallery.com/packages/JoinModule) (see also: [In Powershell, what's the best way to join two tables into one?](https://stackoverflow.com/a/45483110/1701026)): `@{ result = ($Obj1.Values |Join $Obj2.Values -On id -Eq testId -Property id, testType, TestResult) } |ConvertTo-Json` — iRon, Jul 10 '22 at 10:20

score 1 · Accepted Answer · answered Jul 09 '22 at 14:40

Here's some code that will do what you want, but returns in a new array. Let me know if it can't be in a new array. Also, this will work for JSON data where the IDs aren't consistent across the two arrays.

First you have to convert the data from JSON to a PS Object with Convert-FromJson. After that, it's really just looping through one of the arrays.

Since most of the data is coming from the first array, I'll loop through that array. I have to select $obj1.values because $obj1 is an object that has an array where $obj1.values is the array of values.

From there, you will use Where-Object to find a given iteration's corresponding value in the other array.

All of this data goes into a PS Custom Object that then gets pushed to an array for your answer.

# Array 1 - $MyResultArray1
$obj1 = '{
  "values": [
    {
       "id": 1,
       "testType": "something1"
    },
    {
       "id": 2,
       "testType": "something2"
    },
    {
       "id": 3,
       "testType": "something3"
    }
  ]
}' | ConvertFrom-Json

# Array 2 - $MyResultArray2
$obj2 = '{
  "values": [
    {
       "testId": 1,
       "testResult": "result1",
       "testMeta": {}
    },
    {
       "testId": 2,
       "testResult": "result2",
       "testMeta": {}
    },
    {
       "testId": 3,
       "testResult": "result3",
       "testMeta": {}
    }
  ]
}' | ConvertFrom-Json

$newArr = @()


foreach ($value in $obj1.values) {
    $newObj = [pscustomobject]@{
        id = $value.id
        testType = $value.testType
        testResult = ($obj2.values | Where-Object { $_.testId -eq $value.id }).testResult
    }

    $newArr += $newObj
    #$value.testResult = $obj2.values | Where-Object { $_.testId -eq $value.id }
}

Write-Host $newArr
<#
    @{id=1; testType=something1; testResult=result1}
    @{id=2; testType=something2; testResult=result2} 
    @{id=3; testType=something3; testResult=result3}
#>

Hope this helps... Let me know if you need something more :)

Thanks, what happens in case if `Array 2` not having the "testId" matching to the "id" will Where-Object return blank? UPDATE: I just tested it and it returns `null` can we change it to empty string? — User7723337, Jul 09 '22 at 14:53
Add `if (!($newObj.testResult)) { $newObj.testResult = "" }` after `$newObj` creation — broffutt, Jul 09 '22 at 14:57
`| %{if($_){$_}else{""}}` Adding this pipeline is the only thing I can think of at the moment — broffutt, Jul 09 '22 at 15:31
You could put a ternary in there, but not all versions of PS have ternary statements available — broffutt, Jul 09 '22 at 15:32
Thanks, One more thing. I want to convert `$newArr` array to JSON, I am using `$finalResult = ConvertTo-Json -Depth 100 -InputObject $newArr`.. When I return `$finalResult` I see `\n` char in the JSON, I think it is getting converted to JSON for two times. — User7723337, Jul 09 '22 at 15:36
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/246297/discussion-between-broffutt-and-user7723337). — broffutt, Jul 09 '22 at 15:41
Try to [avoid using the increase assignment operator (`+=`) to create a collection](https://stackoverflow.com/a/60708579/1701026) as it might get pretty expensive. — iRon, Jul 10 '22 at 10:20

Powershell merge two array based on maaping and generate resulting array

1 Answers1