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
    • #227248
      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
      Topics: 8
      Replies: 606
      Points: 2,344
      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
      Topics: 6
      Replies: 17
      Points: 133
      Helping Hand
      Rank: Participant

      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.

      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
      Topics: 16
      Replies: 1792
      Points: 3,290
      Helping Hand
      Rank: Community Hero

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

Viewing 3 reply threads
  • The topic ‘Merge 2 large arrays based on a single matching property into new PsObject array’ is closed to new replies.