How to join two object arrays in Powershell

Question

I have two arrays of objects in my powershell script

First one has values likes this

@{Id=1, Count=24}
@{Id=2, Count=34}

Second

@{Id=1, Name="Some name"}
@{Id=2, Name="Some other name"}

I like to join these two so I get

@{Id=1, Count=24 Name="Some name"}
etc

Eventually I need to write it to a csv file.

Please edit the question and show how those are assigned values. Those variables look a bit like hash tables, but the syntax is not quite there. — vonPryz, Aug 25 '20 at 09:20

score 2 · Answer 1 · answered Aug 25 '20 at 10:30

Using this Join-Object cmdlet (see also: In Powershell, what's the best way to join two tables into one?) which both (custom) objects (including data tables) and hash tables:

$Array1 = @{Id=1; Count=24},
          @{Id=2; Count=34}
    
$Array2 = @{Id=1; Name="Some name"},
          @{Id=2; Name="Some other name"}

$Array1 | Join $Array2 -On Id

Count Id Name
----- -- ----
   24  1 Some name
   34  2 Some other name

Theo · Accepted Answer · 2020-08-25T10:07:07.990

If your array has objects as you say like

$array1 = [PsCustomObject]@{Id=1; Count=24}, 
          [PsCustomObject]@{Id=2; Count=34}
$array2 = [PsCustomObject]@{Id=1; Name="Some name"}, 
          [PsCustomObject]@{Id=2; Name="Some other name"}

You can do something like this:

# this updates the objects in $array1
foreach ($item in $array1) {
    foreach ($obj in $item) {
        $obj | Add-Member -MemberType NoteProperty -Name 'Name' -Value ($array2 | Where-Object { $_.Id -eq $obj.Id }).Name
    }
}

# show on screen
$array1

# export to CSV
$array1 | Export-Csv -Path 'D:\Test\Joined.Csv' -NoTypeInformation

Output on screen:

Id Count Name           
-- ----- ----           
 1    24 Some name      
 2    34 Some other name

If the arrays store Hashtables like

$array1 = @{Id=1; Count=24}, 
          @{Id=2; Count=34}
$array2 = @{Id=1; Name="Some name"}, 
          @{Id=2; Name="Some other name"}

Then use this:

# this returns a new array of PSObjects
$result = foreach ($item in $array1) {
    foreach ($hash in $item) {
        $hash['Name'] = ($array2 | Where-Object { $_.Id -eq $hash.Id }).Name
    }
    [PsCustomObject]$hash
}

# show on screen
$result

# export to CSV
$result | Export-Csv -Path 'D:\Test\JoinedFromHashes.Csv' -NoTypeInformation

Output on screen:

Name            Id Count
----            -- -----
Some name        1    24
Some other name  2    34

^{Note that Hashtables are unordered by default, so the order in which the columns appear may vary}

score 1 · Answer 3 · answered Aug 25 '20 at 10:45

For posterity of people coming from google here's an idiomatic way of breaking the problem down.

$first = $(
    @{Id=1; Count=24}
    @{Id=2; Count=34}
)
$second = $(
    @{Id=1; Name="Some name"}
    @{Id=2; Name="Some other name"}
)

$first,$second `
| %{ $_ } `
| To-Object `
| Group-Object -Property Id `
| %{ $_.Group | To-Hash | Merge } `
| To-Object `
| ConvertTo-Csv

If you want to keep them as hashtables and not get the CSV just yet, remove the last two lines, or if they were objects to begin with remove the first To-Object.

For which you'd need the following:

Filter To-Object {
    $output = [PSCustomObject]@{}
    $_.GetEnumerator() `
    | %{
        $output `
        |  Add-Member `
            -Type NoteProperty `
            -Name $_.Name `
            -Value $_.Value
    }
    $output
}

Filter To-Hash {
    $out = @{}
    $in = $_
    $in `
    | Get-Member -MemberType NoteProperty `
    | %{
        $out[$_.Name] = $in.($_.Name)
    }
    $out
}

Function Merge {
    Begin {
        $output = @{}
    }
    Process {
        $_.GetEnumerator() `
        | %{
            $output[$_.Name] = $_.Value
        }
    }
    End {
        $output
    }
}

Not as efficient as my other answer (built-for-purpose) but these'll be much more useful for things outside of the asker's exact usecase, and much better for anyone learning powershell to have a look at :)

Interesting approach. Ideas for improvements: 1) simplify `To-Object` to `{ New-Object PSCustomObject -Property $_ }` 2) ditch `To-Hash` by retooling `Merge` to work with objects. — MarredCheese, Sep 28 '21 at 02:22

score 0 · Answer 4 · answered Aug 25 '20 at 10:09

$first = $(
    @{Id=1; Count=24}
    @{Id=2; Count=34}
)
$second = $(
    @{Id=1; Name="Some name"}
    @{Id=2; Name="Some other name"}
)

$first,$second `
| %{ $_ } `
| &{
    Param ($key)
    Begin {
        $output = @{}
        
        Function Input {
            Param ($id)
            Begin {
                $merged = $output[$id]
                if (!$merged) {
                    $merged = [PSCustomObject]@{$key=$id}
                }
            }
            Process {
                if ($_.Name -ne $key) {
                    $merged `
                    |  Add-Member `
                        -Type NoteProperty `
                        -Name $_.Name `
                        -Value $_.Value
                }
            }
            End {
                $output[$id] = $merged
            }
        }
    }
    Process {
        $_.GetEnumerator() `
        | Input -id $_[$key]
    }
    End {
        $output.Values
    }
} -key 'Id' `
| ConvertTo-Csv

Outputs

Id Count Name
-- ----- ----
 2    34 Some other name
 1    24 Some name

Then

"Id","Count","Name"
"2","34","Some other name"
"1","24","Some name"

How to join two object arrays in Powershell

4 Answers4