Thanks to my work on Entity Framework’s Code First Migrations NuGet PowerShell commands, I’ve gained quite a bit of experience programming in PowerShell. In this post, I want to show you some of PowerShel’s query operators.

In PowerShell, you pipe commands together and pump data through them to get a result. In order to do this, we need some data. Here’s some data loosely based on the Chinook Database, my favorite sample database.

$albums = @(
    [PSCustomObject]@{
        Artist = 'Anberlin'
        Title = 'Never Take Friendship Personal'
        Tracks = 11
    }
    [PSCustomObject]@{
        Artist = 'Anberlin'
        Title = 'Cities'
        Tracks = 12
    }
    [PSCustomObject]@{
        Artist = 'Angels & Airwaves'
        Title = 'We Don''t Need to Whisper'
        Tracks = 10
    }
    [PSCustomObject]@{
        Artist = 'Something Corporate'
        Title = 'Leaving Through The Window'
        Tracks = 14
    }
)

Projecting

To project only certain properties of objects, use the select command. Here is an example of selecting just the Artist properties.

$albums | select Artist

You can select only distinct results using the -Unique parameter.

$albums | select Artist -Unique

Paging

You can also page results using the select command’s -Skip, -First, and -Last parameters. Here’s how to get the first album.

$albums | select -First 1

Here’s how to get the second album.

$albums | select -First 1 -Skip 1

Here’s how to get the last one.

$albums | select -Last 1

Filtering

Filtering, like in most languages, is done using the where command. The following is an example of selecting all the albums with an artist who’s name starts with an A.

$albums | where Artist -like 'A*'

You can also do it as an expression which allows for more complex filtering.

$albums | where { $_.Artist.StartsWith('A') }

Ordering

To order the results, use the sort command. Let’s order the albums by number of tracks.

$albums | sort Tracks

Now, let’s do it in descending order.

$albums | sort Tracks -Descending

Grouping

Grouping is accomplished using the group command. Let’s group the albums by artist.

$albums | group Artist

Aggregating

Basic aggregation can be done using the measure command.

Use the -Average parameter to get the average.

$albums | measure Tracks -Average

To get the maximum value use -Maximum.

$albums | measure Tracks -Maximum

To get the maximum value use -Maximum.

$albums | measure Tracks -Minimum

Finally, to get the summation of all values, use -Sum.

$albums | measure Tracks -Sum

This post is merely an introduction to the ways you can query over data in PowerShell. For more inforamtion, see the official documentation for the following commands.

Querying in PowerShell