Words: 278
Time to read: ~1.5 minute
Update: 2018-03-22 – Had the wrong link to Warren’s post on Join-Object; Added more examples on request.
Update: 2018-03-23 – Code and screenshot for LEFT JOIN was actually for FULL OUTER JOIN. Updated
This is probably going to be the shortest blog post that I write, mainly because it’s a link to another persons blog, but I found this so interesting that I felt I had to share it.
Now we all know that PowerShell has joins but, in this context, I’d call them simple joins
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 'This','is','how','we','join','stuff' -join '–' |
Which gives us the lovely result of…
That’s not what I’m talking about though!
What I’m talking about has even been talked about by the Microsoft PowerShell Team and what I’m talking about is Join-Object
Now I’m going to stop right here and just direct you to the blog post by Warren Frame ( blog | twitter ) where he talks all about it, why he wrote it, and how to use it.
I’ll leave you…
…with a quick example
LEFT JOIN
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| . .\Desktop\In_Progress\PowerShellScripts\Join-Object.ps1 | |
| $ThingsILike = 'Coffee','Whiskey','Food','ToMoveItMoveIt' | ForEach-Object –Process { | |
| [PSCustomObject]@{ | |
| Name = $_ | |
| } | |
| } | |
| $ThingsICanDrink = 'Coffee;$true','Whiskey;$true','Food;$false' | ForEach-Object –Process { | |
| $Name,$CanDrink = $_ -split ';' | |
| [PSCustomObject]@{ | |
| Name = $Name | |
| CanDrink = $CanDrink | |
| } | |
| } | |
| $JoinParams = @{ | |
| Left = $ThingsILike | |
| Right = $ThingsICanDrink | |
| LeftJoinProperty = 'Name' | |
| RightJoinProperty = 'Name' | |
| Type = 'AllInLeft' | |
| Prefix = 'InRight_' | |
| } | |
| Join-Object @JoinParams |
the other option was Sir Mix-a-Lot
INNER JOIN
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| $ThingsILike = 'Coffee', 'Whiskey', 'Food', 'ToMoveItMoveIt' | ForEach-Object –Process { | |
| [PSCustomObject]@{ | |
| Name = $_ | |
| } | |
| } | |
| $ThingsICanDrink = 'Coffee;$true', 'Whiskey;$true', 'Food;$false' | ForEach-Object –Process { | |
| $Name, $CanDrink = $_ -split ';' | |
| [PSCustomObject]@{ | |
| Name = $Name | |
| CanDrink = $CanDrink | |
| } | |
| } | |
| $JoinParams = @{ | |
| Left = $ThingsILike | |
| Right = $ThingsICanDrink | |
| LeftJoinProperty = 'Name' | |
| RightJoinProperty = 'Name' | |
| Type = 'OnlyIfInBoth' | |
| Prefix = 'InRight_' | |
| } | |
| Join-Object @JoinParams |
FULL OUTER JOIN
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| $ThingsILike = 'Coffee', 'Whiskey', 'Food', 'ToMoveItMoveIt' | ForEach-Object –Process { | |
| [PSCustomObject]@{ | |
| Name = $_ | |
| } | |
| } | |
| $ThingsICanDrink = 'Coffee;$true', 'Whiskey;$true', 'Food;$false', 'Ice;"…um…both?"' | ForEach-Object –Process { | |
| $Name, $CanDrink = $_ -split ';' | |
| [PSCustomObject]@{ | |
| Name = $Name | |
| CanDrink = $CanDrink | |
| } | |
| } | |
| $JoinParams = @{ | |
| Left = $ThingsILike | |
| Right = $ThingsICanDrink | |
| LeftJoinProperty = 'Name' | |
| RightJoinProperty = 'Name' | |
| Type = 'AllInBoth' | |
| Prefix = 'InRight_' | |
| } | |
| Join-Object @JoinParams |
Now don’t get me wrong…
…if there is enough data to import this into the database & use T-SQL then you can bet that’s what I’m going to do! It’s what it was designed for, I’d find it easier, and it’s probably going to be faster after you hit a certain threshold.
However, if it’s small sets and the effort of importing the data is going to slow you down and break your flow…
Well, that doesn’t have to be the case anymore.
Amazing what you can do with PowerShell 🙂
This is absolutely brilliant – thanks!