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

Welcome Forums General PowerShell Q&A Merge 2 large arrays based on a single matching property into new PsObject array

Viewing 3 reply threads
  • Author
    Posts
    • #227248
      Participant
      Topics: 1
      Replies: 0
      Points: 11
      Rank: Member

      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.

    • #227296
      Participant
      Topics: 3
      Replies: 340
      Points: 1,120
      Helping Hand
      Rank: Community Hero

      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?

    • #227302
      Participant
      Topics: 4
      Replies: 12
      Points: 89
      Rank: Member

      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.

    • #227341
      Participant
      Topics: 12
      Replies: 1622
      Points: 2,560
      Helping Hand
      Rank: Community Hero

      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/

Viewing 3 reply threads
  • You must be logged in to reply to this topic.