EN VI

Arrays - Filter out values that are equal on the same row?

How to Arrays - Filter out values that are equal on the same row?

I've just run

Get-DbaDbQueryStoreOption -SqlInstance <MySQLServer> | 
Where ActualState -ne DesiredState | 
Select Database, ActualState, DesiredState

My intention was to get all rows from Get-DbaDbQueryStoreOption where ActualState and DesiredState were not identical. To my surprise, this returned rows where ActualState and DesiredState were identical.

Is my PowerShell somehow in error or is this a bug in Get-DbaDbQueryStoreOption?

I believe that this question makes sense even if you know nothing of the Get-DbaDbQueryStoreOption command from the dbatools module. However, the type of ActualState and DesiredState is Selected.Microsoft.SqlServer.Management.Smo.QueryStoreOptions.

Solution:

Assuming that DesiredState too refers to a property of each input object, replace
Where ActualState -ne DesiredState with:

Where { $_.ActualState -ne $_.DesiredState }

Your attempt to use simplified syntax cannot work, because the RHS of an operator such as -ne, which binds to the -Value parameter of Where-Object, must either be a literal, a variable reference or an expression, and none of them are able to refer to the current pipeline input object via the automatic $_ variable, as the latter is only available inside script blocks.

Due to argument syntax, DesiredState in your attempt was interpreted as a bareword string literal, i.e. your attempt was equivalent to
Where ActualState -ne 'DesiredState'

Thus, as shown above, a script block must be used, in which $_ can be used to refer to the current pipeline input object at will, which binds to the -FilterScript parameter.