Perform Set Operations (Union, Intersection, Minus & Complement) Using PowerShell

Recently I wrote an generic object persistor (to DB) in PowerShell. I would love to share it but there are too many depenencies. The subject of this post is however on doing set operations.

The object persistor, simply saves one or more custom objects to a given database table. This involved validation to make sure that the object had atleast the properties “x”, “y”, “z” corresponding to the table columns before we attempt to save it.

i.e., I wanted to make sure that the incoming set of objects had atleast a certain set of properties (mapped to table columns) that the table required values for.

In the process, I wanted to do something generic for the set operations

  • Union
  • Intersection
  • Minus
  • Complement

I did a quick read on Set operations here

http://www.cs.odu.edu/~toida/nerzic/content/set/set_operations.html

http://www.dummies.com/how-to/content/find-the-union-intersection-relative-complement-an.html

…and looked for something in PowerShell around this:

http://stackoverflow.com/questions/8609204/union-and-intersection-in-powershell

I wanted something reusable and well pacakged and proceeded to light the fire using the spark from above.

Since almost everyone already knows these, I will give some examples to illustrate. The only less familiar one is the “Complement”. Basically, it says, from complete universe of values (distinct values from all sets in our case, although it is not intended to be quite that way), subtract the set that needs to be complemented.

For our example, let us consider two sets:

$a = (1,2,3,4)

$b = (1,3,4,5)

The results of the set operations are:

  • UNION: 1,2,3,4,5
  • INTERSECTION: 1,3,4
  • DIFFERENCE: ($a – $b): 2
  • DIFFERENCE: ($b – $a): 5
  • COMPLEMENT $a: 5
  • COMPLEMENT $b: 2
<#
.SYNOPSIS 
    Performs "set" operations

.DESCRIPTION 
    
    Given two sets, does Union, Intersection, Difference and Complement

.INPUTS 
    Two sets

.OUTPUTS 
    The results of the set operation
        
.EXAMPLE 

    $a = (1,2,3,4)
    $b = (1,3,4,5)

    Get-SetOperationResult -Left $a -Right $b -OperationType Union
    1
    3
    4
    5
    2

    Get-SetOperationResult -Left $a -Right $b -OperationType Intersection
    1
    3
    4

    Get-SetOperationResult -Left $a -Right $b -OperationType Difference-LeftMinusRight
    2

    Get-SetOperationResult -Left $a -Right $b -OperationType Difference-RightMinusLeft
    5

    Get-SetOperationResult -Left $a -Right $b -OperationType ComplementLeft
    5

    Get-SetOperationResult -Left $a -Right $b -OperationType ComplementRight
    2

.EXAMPLE 

    #Find the properties in first object that are not in second!
    #  Notice that the Create/Update columns do not exist in the second object

    $databaseTypeObject1 = New-Object System.Object 
    $databaseTypeObject1 | Add-Member -type NoteProperty -name DatabaseType -value 'Oracle'
    $databaseTypeObject1 | Add-Member -type NoteProperty -name Vendor       -value 'Oracle Corporation'
    $databaseTypeObject1 | Add-Member -type NoteProperty -name DatabaseName -value 'Oracle'
    $databaseTypeObject1 | Add-Member -type NoteProperty -name Description  -value 'Oracle database'
    $databaseTypeObject1 | Add-Member -type NoteProperty -name CreateUser   -value $env:USERNAME
    $databaseTypeObject1 | Add-Member -type NoteProperty -name CreateDate   -value (Get-Date)
    $databaseTypeObject1 | Add-Member -type NoteProperty -name UpdateUser   -value $env:USERNAME
    $databaseTypeObject1 | Add-Member -type NoteProperty -name UpdateDate   -value (Get-Date)

    $databaseTypeObject2 = New-Object System.Object 
    $databaseTypeObject2 | Add-Member -type NoteProperty -name DatabaseType -value 'Sybase'
    $databaseTypeObject2 | Add-Member -type NoteProperty -name Vendor       -value 'SAP1'
    $databaseTypeObject2 | Add-Member -type NoteProperty -name DatabaseName -value 'Sybase'
    $databaseTypeObject2 | Add-Member -type NoteProperty -name Description  -value 'Sybase'

    
    Get-SetOperationResult `
        -Left ($databaseTypeObject1 | Get-Member -MemberType Properties | Select-Object -Expand Name) `
        -Right ($databaseTypeObject2 | Get-Member -MemberType Properties | Select-Object -Expand Name) `
        -OperationType Difference-LeftMinusRight

.NOTES 
    Pretty self-explanatory. See "Set Theory" for a refresher link below if needed.

Version History 
    Created by Jana Sattainathan | Twitter @SQLJana | WordPress: SQLJana.WordPress.com
    - Used idea from referenced URL and built this function

.LINK 
    http://stackoverflow.com/questions/8609204/union-and-intersection-in-powershell
    http://www.cs.odu.edu/~toida/nerzic/content/set/set_operations.html
#>


function Get-SetOperationResult
{
    [CmdletBinding()]
    param 
    (
        [Parameter(Mandatory=$true,
                   Position=0)]
        [object[]]
        $Left,

        [Parameter(Mandatory=$true,
                   Position=1)]
        [object[]]
        $Right,

        [Parameter(Mandatory=$false,
                   Position=2)]
        [ValidateSet("Union","Intersection","Difference-LeftMinusRight","Difference-RightMinusLeft","ComplementLeft","ComplementRight")]
        [string]
        $OperationType="Intersection"
    )
    
    
    BEGIN
    {        
    }
    
    PROCESS
    {
        
        [object] $result = @()

        #-----------
        #Union = Given two sets, the distinct set of values from both
        #-----------
        if ($OperationType -eq 'Union')
        {
            $result = Compare-Object $Left $Right -PassThru -IncludeEqual                   # union
        }

        #-----------
        #Intersection = Given two sets, the distinct set of values that are only in both
        #-----------
        if ($OperationType -eq 'Intersection')
        {
            $result = Compare-Object $Left $Right -PassThru -IncludeEqual -ExcludeDifferent # intersection
        }

        #-----------
        #Difference = Given two sets, the values in one (minus) the values in the other
        #-----------
        if ($OperationType -eq 'Difference-LeftMinusRight')
        {
            $result = $Left | ?{-not ($Right -contains $_)}
        }
        if ($OperationType -eq 'Difference-RightMinusLeft')
        {
            $result = $Right | ?{-not ($Left -contains $_)}
        }
        
        #-----------
        #Complement = Given two sets, everything in the universe which is the UNION (minus) the values in the set being "Complemented"
        #-----------
        if ($OperationType -eq 'ComplementLeft')
        {
            $result = Compare-Object $Left $Right -PassThru -IncludeEqual |                  # union
                                ?{-not ($Left -contains $_)}
        }
        if ($OperationType -eq 'ComplementRight')
        {
            $result = Compare-Object $Left $Right -PassThru -IncludeEqual |                  # union
                                ?{-not ($Right -contains $_)}
        }
        
        Write-Output $result
    }
    
    END 
    {
    }
}

Hopefully the examples are self-explanatory. Specifically, the custom object example shows how I ended up solving the original problem that prompted this post!

This example (also in the code above) shows how to get the list of properties that are in the first object but are not in the second:

#Find the properties in first object that are not in second!
    #  Notice that the Create/Update columns do not exist in the second object

    $databaseTypeObject1 = New-Object System.Object 
    $databaseTypeObject1 | Add-Member -type NoteProperty -name DatabaseType -value 'Oracle'
    $databaseTypeObject1 | Add-Member -type NoteProperty -name Vendor       -value 'Oracle Corporation'
    $databaseTypeObject1 | Add-Member -type NoteProperty -name DatabaseName -value 'Oracle'
    $databaseTypeObject1 | Add-Member -type NoteProperty -name Description  -value 'Oracle database'
    $databaseTypeObject1 | Add-Member -type NoteProperty -name CreateUser   -value $env:USERNAME
    $databaseTypeObject1 | Add-Member -type NoteProperty -name CreateDate   -value (Get-Date)
    $databaseTypeObject1 | Add-Member -type NoteProperty -name UpdateUser   -value $env:USERNAME
    $databaseTypeObject1 | Add-Member -type NoteProperty -name UpdateDate   -value (Get-Date)

    $databaseTypeObject2 = New-Object System.Object 
    $databaseTypeObject2 | Add-Member -type NoteProperty -name DatabaseType -value 'Sybase'
    $databaseTypeObject2 | Add-Member -type NoteProperty -name Vendor       -value 'SAP1'
    $databaseTypeObject2 | Add-Member -type NoteProperty -name DatabaseName -value 'Sybase'
    $databaseTypeObject2 | Add-Member -type NoteProperty -name Description  -value 'Sybase'

    
    Get-SetOperationResult `
        -Left ($databaseTypeObject1 | Get-Member -MemberType Properties | Select-Object -Expand Name) `
        -Right ($databaseTypeObject2 | Get-Member -MemberType Properties | Select-Object -Expand Name) `
        -OperationType Difference-LeftMinusRight

Results:
  CreateDate
  CreateUser
  UpdateDate
  UpdateUser

Advertisements

3 thoughts on “Perform Set Operations (Union, Intersection, Minus & Complement) Using PowerShell

  1. I have an improvement to recommend. Drop -OperationType parameter and make -Union, -Intersection, -Difference and -Complement switch parameters (like the simplified syntax for Where-Object added in PowerShell version 3.0). So you can:

    Get-SetOperationResult $left -Union $right

    You’ll also have to reorder the parameters.

  2. Good idea. I will keep that in mind.

    However, would it not break it for PowerShell V2, which most people don’t update on the servers running 2008 and on Windows 7 PC’s?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s