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

Question

I am trying to create a table from a JSON file that I get from the RESTful API.

When I print a property of a json object, I get this output:

  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 result that I would like to see is as follows:

  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 its 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 key as the Name property 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, by default, displays the result as a list, since your objects have more than 4 properties. Pull it through Format-List -AutoSize to get table output.

Answer 2

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

Let it set an input sample that mimics a single nested object converted to a PowerShell [pscustomobject] ConvertFrom-Json 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'} } 

The output of $objFromJson gives a result formatted as in the question.

Why does this lead to the formatting of the output indicated in the question?

For types like [pscustomobject] that do not have explicit formatting definitions defined for them (via *.ps1xml files and loaded implicitly in the session or explicitly through Update-FormatData ), PowerShell decides which default formatting to use depending on the number properties of this 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 supposedly shortened; with really only 4 properties, a tabular display would lead.}

Properties themselves are rendered by calling .PSObject.ToString() based on their values, which is usually the same representation that you would get if you referenced an object inside a string with two quotes, except that the latter always uses cultural invariant formatting, whereas .ToString() will respect the current culture if the type supports it.

In the case of the [pscustomobject] instance, this results in a view that resembles a hash table literal but is not one (see this answer for reference); eg:.

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

Reformatting the data as desired:

It is not possible to use formatting cmdlets such as Format-Table to get the desired result — you must first change the data:

In particular, the properties of the $objFromJson must be changed to a set of user objects:

• The Name property contains the name of this property and

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

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

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

Outputting a collection of $objFromJson property $objFromJson and retrieving only the Name and Value property definitions is a step in the right direction:

 > $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 property properties of the .Value property for the returned objects to output with individual property values.

Ansgar's elegant answer demonstrates how to do this in a single pipeline.
Let me supplement it with an auxiliary 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 this function in place, you can now achieve the desired output:

 ConvertTo-Collection $objFromJson | Format-Table 

To exclude a specific property, for example key3 :

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