Querying in PowerShell
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
More
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.