How to use Sort-Object and Where-Object in Select-Object in PowerShell?

Question

I'm writing a PowerShell script for Azure PowerShell 3.1.0:

1. Get content from JSON
2. Looping through the content based on a condition where MigratedFlag='Y' and sort ascending onOrderNo
3. Executing the SQL file from Location using Invoke-Sqlcmd
4. If my location of value is equal to executed script then update MigratedFlag=N

I'm able to do all steps except step 2 i.e. sorting by OrderNo and adding condition on MigratedFlag,

Here is a snippet of JSON content:

[
    {
        "OrderNo":  "1",
        "Location":  "ETS\\Stage_PS\\FS_PS_CUSTOMER.sql",
        "MigratedFlag":  "Y",
        "Description":  "Creation of STG_PS_FS.PS_CUSTOMER"
    },
    {
        "OrderNo":  "2",
        "Location":  "ETS\\Stage_PS\\FS_PS_CUST_ADDRESS.sql",
        "MigratedFlag":  "Y",
        "Description":  "Creation of STG_PS_FS.PS_CUST_ADDRESS"
    },
    {
        "OrderNo":  "3",
        "Location":  "ETS\\Stage_PS\\FS_PS_CUST_ADDR_SEQ.sql",
        "MigratedFlag":  "Y",
        "Description":  "Creation of STG_PS_FS.PS_CUST_ADDR_SEQ"
    }
]

Below is the PS script, I'm using, (unable to use Sort-Object nor Where-Object in Select-Object):

$v_JSON = Get-Content '$(system.defaultworkingdirectory)\xxxxx\BuildOrder.json' -raw | ConvertFrom-Json
$v_JSON | Select-Object -Property Location | ForEach {
$Script =  $_.Location
Write-Host "Executing Script"$Script
Invoke-Sqlcmd -ServerInstance "InstanceName" -Database $(database) -Username $(testauto_username)  -Password $(testauto_password) -InputFile $(system.defaultworkingdirectory)\$Script
$v_JSON | % {if($_.Location -eq $Script){$_.MigratedFlag='N'}}
$v_JSON | ConvertTo-Json -depth 32| set-content '$(system.defaultworkingdirectory)\xxxxx\BuildOrder.json'
}

Answer 1

Just insert them into the pipe before Select-Object:

$v_JSON = Get-Content '$(system.defaultworkingdirectory)\xxxxx\BuildOrder.json' -Raw | ConvertFrom-Json
$v_JSON | Sort-Object -Property OrderNo | Where-Object {$_.MigratedFlag -like 'Y'} | Select-Object -Property Location | ForEach {
    $Script =  $_.Location
    Write-Host "Executing Script"$Script
    Invoke-Sqlcmd -ServerInstance "InstanceName" -Database $(database) -Username $(testauto_username)  -Password $(testauto_password) -InputFile $(system.defaultworkingdirectory)\$Script
    $v_JSON | % {if($_.Location -eq $Script){$_.MigratedFlag='N'}}
    $v_JSON | ConvertTo-Json -depth 32| set-content '$(system.defaultworkingdirectory)\xxxxx\BuildOrder.json'
}

Btw: Your Select-Object -Property Location is not that usefull as you have to iterate inside your loop again over $V_JSON to get other properties. I recommend the following optimization:

$v_JSON = Get-Content '$(system.defaultworkingdirectory)\xxxxx\BuildOrder.json' -Raw | ConvertFrom-Json
$v_JSON | Sort-Object -Property OrderNo | Where-Object {$_.MigratedFlag -like 'Y'} | ForEach {
    $Script =  $_.Location
    Write-Host "Executing Script: $Script"
    Invoke-Sqlcmd -ServerInstance "InstanceName" -Database $(database) -Username $(testauto_username)  -Password $(testauto_password) -InputFile $(system.defaultworkingdirectory)\$Script
    $_.MigratedFlag = 'N'
}
$v_JSON | ConvertTo-Json -depth 32| set-content '$(system.defaultworkingdirectory)\xxxxx\BuildOrder.json'