Join-Object: SQL like joins in PowerShell

Years ago I ran across this wonderful PowerShell cmdlet to Join two objects together based on specific properties.  The original information comes from the PowerShell team blog here, and I’ve posted the code below and I would absolutely recommend checking out their Blog.

Quick and easy to use:

Join-Object -Left $employee -Right $entrance -Where {$args[0].Id -eq $args[1].EmployeeId} –LeftProperties "Name" –RightProperties "When" -Type OnlyIfInBoth

The full function code, thanks again to the PowerShell team blog:

function AddItemProperties($item, $properties, $output){if($item -ne $null){foreach($property in $properties){$propertyHash =$property -as [hashtable]if($propertyHash -ne $null){$hashName=$propertyHash["name"] -as [string]if($hashName -eq $null){throw "there should be a string Name"}$expression=$propertyHash["expression"] -as [scriptblock]if($expression -eq $null){throw "there should be a ScriptBlock Expression"}$_=$item$expressionValue=& $expression$output | add-member -MemberType "NoteProperty" -Name $hashName -Value $expressionValue}else{# .psobject.Properties allows you to list the properties of any object, also known as "reflection"foreach($itemProperty in $item.psobject.Properties){if ($itemProperty.Name -like $property){$output | add-member -MemberType "NoteProperty" -Name $itemProperty.Name -Value $itemProperty.Value}}}}}}function WriteJoinObjectOutput($leftItem, $rightItem, $leftProperties, $rightProperties, $Type){$output = new-object psobjectif($Type -eq "AllInRight"){# This mix of rightItem with LeftProperties and vice versa is due to# the switch of Left and Right arguments for AllInRightAddItemProperties $rightItem $leftProperties $outputAddItemProperties $leftItem $rightProperties $output}else{AddItemProperties $leftItem $leftProperties $outputAddItemProperties $rightItem $rightProperties $output}$output}<#.SynopsisJoins two lists of objects.DESCRIPTIONJoins two lists of objects.EXAMPLEJoin-Object $a $b "Id" ("Name","Salary")#>function Join-Object{[CmdletBinding()][OutputType([int])]Param(# List to join with $Right[Parameter(Mandatory=$true,Position=0)][object[]]$Left,# List to join with $Left[Parameter(Mandatory=$true,Position=1)][object[]]$Right,# Condition in which an item in the left matches an item in the right# typically something like: {$args[0].Id -eq $args[1].Id}[Parameter(Mandatory=$true,Position=2)][scriptblock]$Where,# Properties from $Left we want in the output.# Each property can:# - Be a plain property name like "Name"# - Contain wildcards like "*"# - Be a hashtable like @{Name="Product Name";Expression={$_.Name}}. Name is the output property name#   and Expression is the property value. The same syntax is available in select-object and it is#   important for join-object because joined lists could have a property with the same name[Parameter(Mandatory=$true,Position=3)][object[]]$LeftProperties,# Properties from $Right we want in the output.# Like LeftProperties, each can be a plain name, wildcard or hashtable. See the LeftProperties comments.[Parameter(Mandatory=$true,Position=4)][object[]]$RightProperties,# Type of join.#   AllInLeft will have all elements from Left at least once in the output, and might appear more than once# if the where clause is true for more than one element in right, Left elements with matches in Right are# preceded by elements with no matches. This is equivalent to an outer left join (or simply left join)# SQL statement.#  AllInRight is similar to AllInLeft.#  OnlyIfInBoth will cause all elements from Left to be placed in the output, only if there is at least one# match in Right. This is equivalent to a SQL inner join (or simply join) statement.#  AllInBoth will have all entries in right and left in the output. Specifically, it will have all entries# in right with at least one match in left, followed by all entries in Right with no matches in left,# followed by all entries in Left with no matches in Right.This is equivallent to a SQL full join.[Parameter(Mandatory=$false,Position=5)][ValidateSet("AllInLeft","OnlyIfInBoth","AllInBoth", "AllInRight")][string]$Type="OnlyIfInBoth")Begin{# a list of the matches in right for each object in left$leftMatchesInRight = new-object System.Collections.ArrayList# the count for all matches$rightMatchesCount = New-Object "object[]" $Right.Countfor($i=0;$i -lt $Right.Count;$i++){$rightMatchesCount[$i]=0}}Process{if($Type -eq "AllInRight"){# for AllInRight we just switch Left and Right$aux = $Left$Left = $Right$Right = $aux}# go over items in $Left and produce the list of matchesforeach($leftItem in $Left){$leftItemMatchesInRight = new-object System.Collections.ArrayList$null = $leftMatchesInRight.Add($leftItemMatchesInRight)for($i=0; $i -lt $right.Count;$i++){$rightItem=$right[$i]if($Type -eq "AllInRight"){# For AllInRight, we want $args[0] to refer to the left and $args[1] to refer to right,# but since we switched left and right, we have to switch the where arguments$whereLeft = $rightItem$whereRight = $leftItem}else{$whereLeft = $leftItem$whereRight = $rightItem}if(Invoke-Command -ScriptBlock $where -ArgumentList $whereLeft,$whereRight){$null = $leftItemMatchesInRight.Add($rightItem)$rightMatchesCount[$i]++}}}# go over the list of matches and produce outputfor($i=0; $i -lt $left.Count;$i++){$leftItemMatchesInRight=$leftMatchesInRight[$i]$leftItem=$left[$i]if($leftItemMatchesInRight.Count -eq 0){if($Type -ne "OnlyIfInBoth"){WriteJoinObjectOutput $leftItem  $null  $LeftProperties  $RightProperties $Type}continue}foreach($leftItemMatchInRight in $leftItemMatchesInRight){WriteJoinObjectOutput $leftItem $leftItemMatchInRight  $LeftProperties  $RightProperties $Type}}}End{#produce final output for members of right with no matches for the AllInBoth optionif($Type -eq "AllInBoth"){for($i=0; $i -lt $right.Count;$i++){$rightMatchCount=$rightMatchesCount[$i]if($rightMatchCount -eq 0){$rightItem=$Right[$i]WriteJoinObjectOutput $null $rightItem $LeftProperties $RightProperties $Type}}}}}