PowerShell 5.1 I am not getting the expected output in this simple example

Question

$res = Invoke-Sqlcmd -Query "select * from customer" -ServerInstance "(localdb)\MSSQLLocalDB" -Database "Database1" -OutputAs DataTables

$res | Where-Object FirstName -eq "John"
$res.Where({$_.FirstName -eq "John"})

This is the output:

Id FirstName City   
-- --------- ----   
 1 John      Augusta
 1 John      Augusta

I was expecting this:

Id FirstName City   
-- --------- ----   
 1 John      Augusta

Id FirstName City   
-- --------- ----   
 1 John      Augusta

Answer 1

Building on the helpful comments:

The behavior is by design:

• Objects output by a given script or a single interactively submitted command line are all sent to the success output stream of a single pipeline.

• When output from a pipeline is neither captured nor redirected, PowerShell applies for-display output formatting to all output objects in the success output stream of a given pipeline, and if the first output object implicitly selects tabular formatting, all subsequent objects of the same type are formatted as part of the same table that is printed to the host (display, terminal).

  • Things get tricky if subsequent objects are of a different type as well as if they're also [pscustomobject] instances, but with different property sets - see this answer for more information.

---

If you want to format a given command's output individually, you have three basic options, all of which are suboptimal in case you also want to option to later programmatically process the output, not just format it for display:

• Send the output directly to the host, using Out-Host:

  $res | Where-Object FirstName -eq "John" | Out-Host     
  

  • This bypasses the success output stream, meaning that this output cannot be captured or redirected.

• Use a Format-* cmdlet such as Format-Table explicitly:

  $res | Where-Object FirstName -eq "John" | Format-Table
  

  • This sends objects representing formatting instructions rather than the original objects to the success output stream, which the host (as the default output target) renders correctly, but these objects are meaningless for further programmatic processing.

• Use Out-String (possibly preceded by a call to a Format-* cmdlet to select the kind of formatting and/or control the specifics of the formatting):

  $res | Where-Object FirstName -eq "John" | Out-String
  

  • This sends a single, multi-line string to the success output stream that contains the same representation you would see in the host with Out-Host^[1] and since strings always render as-is, you'll see the same host output; in programmatic processing, these strings are relatively more meaningful than the formatting-instruction objects output by Format-* cmdlets, but still amount to a loss of information compared to the original output objects.

---

^{[1] Unfortunately, Out-String always appends a trailing newline to this representation; this problematic behavior is the subject of GitHub issue #14444. As zett42 points out, using Out-String -Stream avoids this problem, albeit at the expense of sending the lines of the multi-line string representation individually to the output stream; To avoid that, you can use (... | Out-String -Stream) -join "`n"}