Merge 2 large arrays based on a single matching property into new PsObject array

Hi All, OK... this is frying my brain!!!
I have 2 large arrays which contain information about items e.g.
  • $array1 = @($name,$description,$cost,$manufacturer,$active,$id) - actual array has 32 properties
  • $array2 = @($productid,$location,$quantity,$max,$min,$reserved) - actual array has 11 properties
The only properties that contain matching values in both arrays are $id and $productid (and they are not indexed in the same location) and I need to merge each object into a new PsObject containing the correct values from both arrays (I am not creating the original arrays, they are pulled from an API) each array contains several thousand items.

Alright, sounds fun. What have you tried? Also, i want to make sure I understand correctly. You want to end up with one object that contains all the properties from both objects combined? Do you want to leave both Id and productid properties? Are there identical properties in the two object? If so, are you wanting to keep them all? If so and you don’t want to keep them all, which objects property wins?

Sounds like you’re wanting essentially an SQL Join statement, but for PowerShell. It won’t be performant, so throw that idea away. I think you would have to inspect each object in one of the arrays, grab a reference to an object in the other array, then spit out a new object with all the properties you want. Here’s this to get you something - hopefully someone else has some smarter way of doing it.

$NewArray = foreach ($arr1 in $array1) {
    $arr2 = $array2.where{$_.productid -eq $arr1.id}
    [pscustomobject]@{
        Name=$Arr1.name
        Description = $Arr1.Description
        Cost = $arr1.Cost
        Manufacturer = $arr1.Manufacturer
        active = $arr1.Active
        id = $arr1.ID
        ### All other 26 properties from Arr1
        location = $arr2.location
        Quantity = $arr2.Quantity
        Max = $arr2.Max
        Min = $arr2.Min
        Reserved = $arr2.Reserved
        ### All 11 properties from Array2
    }
}

With me being as crazy as I am, if I had to regularly deal with that many objects, I’d use PowerShell to grab the items, and put them in a database, then use an SQL view to do that kind of Join work, as that would be a lot faster for that kind of thing.

If you want to do different types of joins, folks have created some functions that will do the work:

http://ramblingcookiemonster.github.io/Join-Object/