Inner Join in PowerShell (without SQL)

Question

How do we make Inner-Join or something a Cross-Join in PowerShell or PowerCLI?

Even though im new to PowerCLI/PowerShell , I do have a basic grasp on them, yet have practically spent 2 days trying to figure this, going through numerous documentations and blogs to no avail.

All I really want to know is if after typing my command

Get-Content File.txt

and getting:

Output1 or Table1 is

Name: Abc
Group: Bad
Policy: Great

Name: redi
Group: Good 
Policy: MAD

etc. etc.

100s of these, and obviously more than just the 3 elements of Name, Group, Policy each.

Table2/Output2

Name: Abc
Limit: 10
used: 5

Name: redi
Limit: 20
used: 1

etc. etc.

100s of these.

and like 13 more of these text file tables, all with the "Name" as unique.

How can I combine it into one output at the end using Name with all the other elements?

My most obvious thought was something akin to joins, even if I had to do them 1 at a time, but even that I cant figure out how to do.

Is there anyway to do this in PowerShell itself without me having to go into Python or SQL?

If yes is there a method that is able to combine fields in spots where it's null?

If its not clear what type of result I am hoping for it will look something akin to this:

Name: Abc
Group: Bad
Policy: Great
Limit: 10
used: 5


Name: redi
Group: Good 
Policy: MAD
Limit: 20
used: 1

Paweł Dyl · Answer 1 · 2016-09-22T07:03:18.360

You can use simple loop join as follows:

$table1 = [pscustomobject]@{Name='Abc';Group='Bad';Policy='Great'},[pscustomobject]@{Name='redi';Group='Good ';Policy='MAD'}
$table2 = [pscustomobject]@{Name='Abc';Limit=10;used=5},[pscustomobject]@{Name='redi';Limit=20;used=1}

$table1 | % { 
                foreach ($t2 in $table2) {
                    if ($_.Name -eq $t2.Name) {
                        [pscustomobject]@{Name=$_.Name;Group=$_.Group;Policy=$_.Policy;Limit=$t2.Limit;Used=$t2.Used}
                    }
                }
            }

Assuming uniqueness of keys you can also use faster, hashtable approach:

$hashed = $table1 | group Name -AsHashTable
$table2 | % {
    $matched = $hashed[$_.Name]
    if ($matched) {
        [pscustomobject]@{Name=$matched.Name;Group=$matched.Group;Policy=$matched.Policy;Limit=$_.Limit;Used=$_.Used}
    }
}

You can also use generic solution and wrap it in function. It matches records by their property names:

function Join-Records($tab1, $tab2){
    $prop1 = $tab1 | select -First 1 | % {$_.PSObject.Properties.Name} #properties from t1
    $prop2 = $tab2 | select -First 1 | % {$_.PSObject.Properties.Name} #properties from t2
    $join = $prop1 | ? {$prop2 -Contains $_}
    $unique1 = $prop1 | ?{ $join -notcontains $_}
    $unique2 = $prop2 | ?{ $join -notcontains $_}


    if ($join) {
        $tab1 | % {
            $t1 = $_
            $tab2 | % {
                $t2 = $_
                foreach ($prop in $join) {
                    if (!$t1.$prop.Equals($t2.$prop)) { return; }
                }
                $result = @{}                
                $join | % { $result.Add($_,$t1.$_) }
                $unique1 | % { $result.Add($_,$t1.$_) }
                $unique2 | % { $result.Add($_,$t2.$_) }
                [PSCustomObject]$result
            }
        }
    }
}

$table1 = [pscustomobject]@{Name='Abc';Group='Bad';Policy='Great'},
    [pscustomobject]@{Name='redi';Group='Good ';Policy='MAD'},
    [pscustomobject]@{Name='Not joined';Group='Very bad';Policy='Great'}
$table2 = [pscustomobject]@{Name='Abc';Limit=10;used=5},
    [pscustomobject]@{Name='redi';Limit=20;used=1},
    [pscustomobject]@{Name='redi';Limit=20;used=2}

#name is only common property, records joined by name
Join-Records $table1 $table2

#example2
$test1 = [pscustomobject]@{A=1;B=1;C='R1'},
    [pscustomobject]@{A=1;B=2;C='R2'},
    [pscustomobject]@{A=2;B=2;C='R3'}

$test2 = [pscustomobject]@{A=1;B=1;D='R4'},
    [pscustomobject]@{A=3;B=2;D='R5'},
    [pscustomobject]@{A=4;B=2;D='R6'}

Join-Records $test1 $test2 #joined by two common columns - A and B

You can also cascade calls:

$test1 = [pscustomobject]@{A=1;B=1;C='R1'},
    [pscustomobject]@{A=1;B=2;C='R2'},
    [pscustomobject]@{A=2;B=2;C='R3'}

$test2 = [pscustomobject]@{A=1;B=1;D='R4'},
    [pscustomobject]@{A=3;B=2;D='R5'},
    [pscustomobject]@{A=4;B=2;D='R6'}

$test3 = [pscustomobject]@{B=1;E='R7'},
    [pscustomobject]@{B=2;E='R8'},
    [pscustomobject]@{B=3;E='R9'}

#first join by common A and B, then join result by common B
Join-Records (Join-Records $test1 $test2) $test3

Thanks... but I am Wondering if I have to type out my long list of key value pairs? ..... would have $table1= Get-Content file1.txt |Select Name etc and having $table2 and 3 etc be similar; would that work as a replacement to your above $table1 with [pscustomobject ] and key value pairs .....Also can this be done with more than 2 tables? — AdilZ, Sep 22 '16 at 06:54

score 4 · Answer 2 · edited May 23 '17 at 12:19

Paweł Dyl provided you a solution based on your two tables. However you probably need a generic solution where you don't have to specify each property by name yourself.

I would combine each table to a an array. Group the tables on the Name property using the Group-Object cmdlet. Iterate over each group and create a PsObject using the properties:

$table1 = [PSCustomObject]@{ Name = 'Abc'; Group = 'Bad'; Policy = 'Great'}, [PSCustomObject]@{ Name = 'redi'; Group = 'Good'; Policy = 'MAD'}
$table2 = [PSCustomObject]@{ Name = 'Abc'; Limit = '10'; used = '5'}, [PSCustomObject]@{ Name = 'redi'; Limit = '20'; used = '1'}

$allTables = $table1 + $table2

$allTables | group Name | Foreach {
    $properties = @{}
    $_.Group | Foreach {
        $_.PsObject.Properties | Where Name -ne 'Name' | Foreach {
            $properties +=  @{
                "$($_.Name)" = "$($_.Value)"
            }
        }
    }
    $properties += @{Name = $_.Name}
    New-Object PSObject –Property $properties
}

Output:

Group  : Bad
Policy : Great
Name   : Abc
Limit  : 10
used   : 5

Group  : Good
Policy : MAD
Name   : redi
Limit  : 20
used   : 1

You probably have to parse your text files - you can start another question if you get stuck on it. You can use `| Select-Object name, Group, ...` to specfiy the order of your selection and get the name always on top. Please consider accepting the answer. — Martin Brandl, Sep 22 '16 at 06:14
Thanks I guess what just confirming is that if I can Get-content file.txt and add in your code starting from $table1 = and just tweak it enough to match my table specs cuz I dont want to just type all the different key value pairs, since it defeats the purpose, and wont hold up say when another "name" is added to the whole list, and i run the code again — AdilZ, Sep 22 '16 at 06:29

score 1 · Accepted Answer · answered Sep 22 '16 at 20:00

So I found an Answer which was more suitable and it uses the join-Object function which was defined below:

you can access it at https://github.com/RamblingCookieMonster/PowerShell/blob/master/Join-Object.ps1

All I really had to do was Define my outputs as $A and $B and $C and so on, and just

$Join1=  Join-Object -Left $A -Right $B -LeftJoinProperty Name - RightJoinProperty Name

made $Join2 then 3 so on until I got it all done

$Join2 = Join-Object -Left $Join1 -Right $C -LeftJoinProperty Name -RightJoinProperty Name

$Join3 = Join-Object -Left $Join2 -Right $D -LeftJoinProperty Name -RightJoinProperty Name

$Join4 = Join-Object -Left $Join3 -Right $E -LeftJoinProperty Name -RightJoinProperty Name

Until I got it all done

score 0 · Answer 4 · answered Aug 03 '17 at 12:16

0

$Table1 | Join $Table2 -Using Name
$Table1 | Join $Table2 #Cross Join

See: In Powershell, what's the best way to join two tables into one?

answered Aug 03 '17 at 12:16

iRon

20,463
10
53
79

Inner Join in PowerShell (without SQL)

4 Answers4

Linked