How To Join Object Lists In PowerShell

Question

So I have these two arrays of data objects in my PowerShell code:

$Customers = (
    @{ Id = 1; Name = "Customer 1" },
    @{ Id = 2; Name = "Customer 2"; },
    @{ Id = 3; Name = "Customer 3"; }
);

$Orders = (

    @{ Id = 1; CustomerId = 1; Product = "Red Pumpkin" },
    @{ Id = 2; CustomerId = 1; Product = "Blue Pumpkin" },
    @{ Id = 3; CustomerId = 2; Product = "Green Pumpkin" }
);

How can I mimic a SQL-like JOIN on these two lists and output a new list with the joined data?

Short Answer

Looping and New-Object are your friends:

# loop all customers
foreach ($c in $Customers)
{
    # loop al orders
    foreach ($o in $Orders)
    {
        # decide whether to join
        if ($o.CustomerId -eq $c.Id)
        {
            # output a new object with the join result
            New-Object PSObject -Property @{
                CustomerId = $c.Id;
                OrderId = $o.Id;
                CustomerName = $c.Name;
                OrderProduct = $o.Product;
            };
        }
    }
}

What would be really cool though would be to have a CmdLet that would do this work for us dynamically whilst allowing us to pipe data through, wouldn’t it?

Continue reading “How To Join Object Lists In PowerShell”

Advertisements