Join two results in Powershell

Question

I've got two CMDlets that return lists of objects. One returns objects of the type SPSolution, which contains the property Id, the other returns objects of the type SPFeature with a property SolutionId.

Now I want to join/merge this data something like this:

$f = Get-Feature
$s = Get-Solution
$result = <JOIN> $f $s
          <ON> $f.SolutionId = $s.Id
          <SELECT> FeatureName = $f.DisplayName, SolutionName = $s.Name

Damian Powell · Accepted Answer · 2010-04-20 09:39:05Z

5

It's not efficient, and it assumes PowerShell 2 but it should do the job:

$solutions = Get-Solution

foreach ($f in Get-Feature) {

    $filteredSolutions = $solutions |
        where-object { $_.Id -eq $f.SolutionId }

    foreach ($s in $filteredSolutions) {
        new-object PSObject -prop @{
            FeatureName = $f.DisplayName
            SolutionName = $s.Name
        }
    }
}

Note that I don't have SharePoint installed so I'm afraid that I can't test this!

edited Apr 20, 2010 at 9:39

answered Apr 20, 2010 at 9:30

Damian Powell

8,8557 gold badges52 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Hinek Over a year ago

Thank you, it did the job. But I was really hoping that there is a CMDlet for this kind of operation...

Damian Powell Over a year ago

Here's something very, very old (from when PowerShell was still known as Monad) which describes a Join-Object cmdlet. It's so old it uses a different syntax though: leeholmes.com/blog/CreatingSQLsJoinlikeFunctionalityInMSH.aspx

Keith Hill Over a year ago

There is no Join-Object cmdlet even in PowerShell 2.0.

Damian Powell Over a year ago

@Kieth Hill: the article describes a potential implementation, not something that is part of PowerShell.

Hinek Over a year ago

thanks for the article, I like the implementation. A pity that it only allows to return the first OR the second data source. I'm thinking about building my own Join-Object from there ... or starting a bounty for building it ;-)

|

Chris Rudd · Accepted Answer · 2012-02-24 01:39:20Z

3

Building off what Keith Hill said Making it a 2 liner can greatly improve efficiency. This way you only run Get-Solution once instead of again for every object returned by Get-Feature

$Solutions = Get-Solution
Get-Feature | % {$f = $_; $Solutions | ? {$f.SolutionId -eq $_.Id} | 
                 Select Name,@{n='FeatureName';e={$f.DisplayName}}}

answered Feb 24, 2012 at 1:39

Chris Rudd

8298 silver badges14 bronze badges

Comments

Keith Hill · Accepted Answer · 2010-04-20 15:29:31Z

2

Here's a one-liner that should do the trick (relies on nested pipelines):

Get-Feature | % {$f = $_; Get-Solution | ? {$f.SolutionId -eq $_.Id} | 
                 Select Name,@{n='FeatureName';e={$f.DisplayName}}}

answered Apr 20, 2010 at 15:29

Keith Hill

204k44 gold badges370 silver badges377 bronze badges

2 Comments

Damian Powell Over a year ago

Nice. Didn't realise you could abbreviate 'Name' and 'Expression' to 'n' and 'e'. Works in PS1 too. Although, not hugely readable! ;)

Keith Hill Over a year ago

No, not hugely readable but if you're banging stuff out at the prompt, it is nice to be able to do.

Chandra Sekhar K · Accepted Answer · 2012-02-24 15:31:55Z

1

It's simple and could probably use more work, but it does the job.

function Join-Object {
  param ( [PSObject[]] $objects, $where, $proplist)
    for ($i=0;$i -le $objects.length;$i++) {
      if ($objects[$i+1] -ne $null) {$out += $objects[$i] | %{$o=$_;$objects[$i+1] | where $where | Select -property $proplist} } };
  $out
}

$where is a script block, and $proplist is an array of properties formatted for Select-Object.
It works for passing in two objects. Hope it would work for more, but haven't tried it yet.

edited Feb 24, 2012 at 15:31

Chandra Sekhar K

16.6k11 gold badges71 silver badges91 bronze badges

answered Sep 1, 2011 at 21:44

Chris Kennedy

111 bronze badge

Comments

iRon · Accepted Answer · 2017-08-03 12:10:42Z

0

(Get-Feature | Select @{Name="FeatureName";Expression={$_.DisplayName}) | Join (Get-Solution | Select @{Name="SolutionName";Expression={$_.Name}) SolutionId -eq Id

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

answered Aug 3, 2017 at 12:10

iRon

24.4k10 gold badges60 silver badges107 bronze badges

Collectives™ on Stack Overflow

Join two results in Powershell

5 Answers 5

7 Comments

Comments

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

7 Comments

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related