PowerShell Group Sort Select

Question

I'm imply trying to learn a little and struggling with what I thought would be basic Group By, Order By, ... when working with JSON.

Below is one of my attempts

$response = Invoke-WebRequest -Uri 'https://jsonplaceholder.typicode.com/todos' -UseBasicParsing | ConvertFrom-Json
$response = $response | Sort-Object -property completed, Count
$response = $response | Group-Object -property userId, completed, Count
$response = $response | Select-Object -property name, Count
$response | ft

All I'm trying to do is produce a 3 column table: userId, completed, Count ordered by completed and Count, but can't seem to get it quite right.

The results would end up being like

userId  completed   Count
5   false   8
10  false   8
1   false   9
8   false   9
7   false   11
2   false   12
9   false   12
3   false   13
4   false   14
6   false   14
4   true    6
6   true    6
3   true    7
2   true    8
9   true    8
7   true    9
1   true    11
8   true    11
5   true    12
10  true    12

Could someone help me. Once I get 1 functional example I should be good to go.

Thank you

Answer 1

You want to group on userId - nothing else:

$response = Invoke-WebRequest -Uri 'https://jsonplaceholder.typicode.com/todos' -UseBasicParsing | ConvertFrom-Json
$groupedByUserId = $response |Group-Object -Property userId

This will already give use the correct Count property, and the Name property will have the userId value we're interested in, so all we need to construct is the completed count:

$groupedWithCounts = $groupedByUserId |Select-Object -Property @{Name='userId';Expression='Name'},@{Name='completed';Expression={@($_.Group |Where-Object completed).Count}},Count

At this point we can sort in the desired order (here by completed count, then count, then user id):

$sortedGroups = $groupedWithCounts |Sort-Object completed,Count,userId

Putting it all together:

$response = Invoke-WebRequest -Uri 'https://jsonplaceholder.typicode.com/todos' -UseBasicParsing | ConvertFrom-Json

$groupedByUserId = $response |Group-Object -Property userId
$groupedWithCounts = $groupedByUserId |Select-Object -Property @{Name='userId';Expression='Name'},@{Name='completed';Expression={@($_.Group |Where-Object completed).Count}},Count
$sortedGroups = $groupedWithCounts |Sort-Object completed,Count,userId

$sortedGroups |Format-Table

Result:

userId completed Count
------ --------- -----
4              6    20
6              6    20
3              7    20
2              8    20
9              8    20
7              9    20
1             11    20
8             11    20
10            12    20
5             12    20

---

Much like Select-Object, Sort-Object accepts more complex property expressions than just property names:

PS ~> $groupedWithCounts |Sort-Object @{Expression='completed';Descending=$true},{$_.userId -as [long]}

userId completed Count
------ --------- -----
5             12    20
10            12    20
1             11    20
8             11    20
7              9    20
2              8    20
9              8    20
3              7    20
4              6    20
6              6    20