Format [pscustomobject] instances returned by Invoke-RestMethod or ConvertFrom-Json

Question

I am trying to create a table from a JSON file I am receiving from a RESTful API.

When I print the property of the json object I get an output like this:

PS> Write-Output JSON.Object

Object1           : @{key1=property; key2=property; key3=property; key4=property}
Object2           : @{key1=property; key2=property; key3=property; key4=property}
Object3           : @{key1=property; key2=property; key3=property; key4=property}
Object4           : @{key1=property; key2=property; key3=property; key4=property}

The output I would like to see is this:

Name              key1        key2        key3        key4
-----             ----        ----        ----        ----
Object1           property    property    property    property
Object2           property    property    property    property
Object3           property    property    property    property

In addition, is it possible to avoid displaying a specific key and it's properties?

Example:

Name              key1        key2        key4      # ← Not displaying key3
-----             ----        ----        ----
Object1           property    property    property
Object2           property    property    property
Object3           property    property    property

Answer 1

You need to add the parent keyname as a property Name to the nested objects:

$json.Object | ForEach-Object {
  foreach ($p in $_.PSObject.Properties) {
    $p.Value | Select-Object @{n='Name';e={$p.Name}},*
  }
}

Note that PowerShell will render the output in list form by default, since your objects have more than 4 properties. Pipe it through Format-List -AutoSize to get tabular output.

Answer 2

To complement Ansgar Wiecher's elegant answer with background information:

Let's define sample input that simulates a single, nested object converted to a PowerShell [pscustomobject] instance via ConvertFrom-Json:

$objFromJson = [pscustomobject] @{ 
  Object1 = [pscustomobject] @{key1='o11'; key2='o12'; key3='o13'; key4='o14'}
  Object2 = [pscustomobject] @{key1='o21'; key2='o22'; key3='o23'; key4='o24'} 
  Object3 = [pscustomobject] @{key1='o31'; key2='o32'; key3='o33'; key4='o34'} 
  Object4 = [pscustomobject] @{key1='o41'; key2='o42'; key3='o43'; key4='o44'} 
  Object5 = [pscustomobject] @{key1='o51'; key2='o52'; key3='o53'; key4='o54'} 
}

Outputting $objFromJson gives output that is formatted as in the question.

Why does this result in the output formatting shown in the question?

For types such as [pscustomobject], which do not have explicit formatting definitions defined for them (via *.ps1xml files and loaded implicitly into the session or explicitly via Update-FormatData), PowerShell decides what default formatting to use based on the number of properties of the type:

• A type with up to 4 properties implicitly uses Format-Table
• a type with 5 or more properties implicitly uses Format-List

^{The sample input in the question is presumably abridged; with truly only 4 properties, a tabular display would have resulted.}

The properties themselves are rendered by calling .PSObject.ToString() on their values, which is typically the same representation you'd get if you referenced the object inside a double-quoted string, except that the latter always uses culture-invariant formatting, whereas .ToString() will respect the current culture, if the type supports it.

In the case of a [pscustomobject] instance, this results in a representation that resembles a hashtable literal, but isn't one (for background information, see this answer); e.g.:

PS> $objFromJson.Object1.PSObject.ToString()
@{key1=o11; key2=o12; key3=o13; key4=o14}

Reshaping the data as desired:

There is no way to use formatting cmdlets such as Format-Table directly to yield the desired output - the data must be reshaped first:

Specifically, the properties of object $objFromJson must be reshaped into a collection of custom objects:

• whose Name property contains the name of a given property, and

• whose other properties are the properties of the object of that property's value; in other words: the properties of the input property's value must be made properties of the output object itself.

Extracting $objFromJson's properties is facilitated by PowerShell adding (among others) a hidden .PSObject property to all objects, whose own .Properties property contains a collection of all the object's property definitions (name, value, additional metadata such as the type of property, ...); e.g.:

PS> $objFromJson.Object1.PSObject.Properties
MemberType      : NoteProperty
IsSettable      : True
IsGettable      : True
Value           : o11
TypeNameOfValue : System.String
Name            : key1
IsInstance      : True
# ... remaining properties

Outputting the collection of $objFromJson's property definitions and extracting only the definitions' Name and Value properties is a step in the right direction:

PS> $objFromJson.PSObject.Properties | Select-Object Name, Value

Name    Value                                    
----    -----                                    
Object1 @{key1=o11; key2=o12; key3=o13; key4=o14}
Object2 @{key1=o21; key2=o22; key3=o23; key4=o24}
Object3 @{key1=o31; key2=o32; key3=o33; key4=o34}
Object4 @{key1=o41; key2=o42; key3=o43; key4=o44}
Object5 @{key1=o51; key2=o52; key3=o53; key4=o54}

However, we must make the properties of the .Value property direct properties of the output objects to get output with property-individual values.

Ansgar's elegant answer demonstrates how to do that in a single pipeline.
Let me complement it with a reusable helper function derived from it:

function ConvertTo-Collection($InputObject) {
  foreach ($obj in $InputObject) {
    foreach ($prop in $obj.PSObject.Properties) {
      $prop.Value | Select-Object @{ n='Name'; e={ $prop.Name }}, *
    }
  } 
}

With that function in place, the desired output can now be achieved thus:

ConvertTo-Collection $objFromJson | Format-Table

To exclude a specific property, such as key3:

ConvertTo-Collection $objFromJson | Select-Object -ExcludeProperty key3 |
  Format-Table