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 = @(
        Artist = 'Anberlin'
        Title = 'Never Take Friendship Personal'
        Tracks = 11
        Artist = 'Anberlin'
        Title = 'Cities'
        Tracks = 12
        Artist = 'Angels & Airwaves'
        Title = 'We Don''t Need to Whisper'
        Tracks = 10
        Artist = 'Something Corporate'
        Title = 'Leaving Through The Window'
        Tracks = 14


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


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, 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') }


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 is accomplished using the group command. Let’s group the albums by artist.

$albums | group Artist


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.