Lets say I have an array of objects with some properties, one of which is itself an array of objects. I want to 'flatten' this structure & report each of the embedded objects alongside selected attributes from its parent object.
ExpandProperty seems ideal, but why is the expanded object updated on the source object? and how to prevent this?
Here's a simple test setup:
$json = '[{"Name":"James","Age":42,"Books":[{"Title":"Consider Phlebas","Author":"Iain M. Banks"},{"Title":"The Player of Games","Author":"Iain M. Banks"}]},{"Name":"Susan","Age":36, "Books":[{"Title":"The Three Body Problem","Author":"Cixin Liu"},{"Title":"The Dark Forest","Author":"Cixin Liu"}]}]'
$obj = $json | ConvertFrom-Json
$obj |FT
Name Age Books
---- --- -----
James 42 {@{Title=Consider Phlebas; Author=Iain M. Banks}, @{Title=The Player of Games; Author=Iain M. Banks}}
Susan 36 {@{Title=The Three Body Problem; Author=Cixin Liu}, @{Title=The Dark Forest; Author=Cixin Liu}}
This statement produces exactly the output I am after
$obj | Select-Object -Property Name -ExpandProperty books
Title Author Name
----- ------ ----
Consider Phlebas Iain M. Banks James
The Player of Games Iain M. Banks James
The Three Body Problem Cixin Liu Susan
The Dark Forest Cixin Liu Susan
But the sting in the tail is my original object is modified (the Name property from the parent object has been added to each embedded book object) & if I run the last command again I get the error Select-Object : The property cannot be processed because the property "Name" already exists.
$obj | FT
Name Age Books
---- --- -----
James 42 {@{Title=Consider Phlebas; Author=Iain M. Banks; Name=James}, @{Title=The Player of Games; Author=Iain M. Banks; Name=James}}
Susan 36 {@{Title=The Three Body Problem; Author=Cixin Liu; Name=Susan}, @{Title=The Dark Forest; Author=Cixin Liu; Name=Susan}}
This just seems weird. I thought select-object created a new object as output from each input object, but ExpandProperty appears to update the source object. How can I get the output I want without modifying the input object, and such that I can run the statement again without error
The issue is explained in the -ExpandProperty description, in Note and Warning. Basically, when -Property and -ExpandProperty are used together, the cmdlet will attach a NoteProperty to the input objects, this is why a consecutive Select-Object call with the same arguments throws an error, the properties are already added.
If you want to avoid this issue you could first:
$obj | Select-Object -Property Name -ExpandProperty books
And then, in consecutive calls refer to just:
$obj.Books
However, if you do not want to touch the original objects then the extremely cumbersome solution can be to use a calculated property with help of loop:
$obj | ForEach-Object { $i = $_; $_.Books } | Select-Object *, @{ N='Name'; E={ $i.Name }}
A little function that can overcome the issue with Select-Object:
function expand {
param(
[Parameter(ValueFromPipeline)]
[object] $InputObject,
[Parameter(Position = 0)]
[string] $ExpandProperty,
[Parameter(Position = 1)]
[ValidateNotNull()]
[SupportsWildcards()]
[string[]] $Property
)
begin {
$dict = [ordered]@{}
$Property = [System.Collections.Generic.HashSet[string]]::new(
$Property, [System.StringComparer]::OrdinalIgnoreCase)
}
process {
$propertyInfo = $InputObject.PSObject.Properties
foreach ($object in $InputObject.$ExpandProperty) {
foreach ($prop in $object.PSObject.Properties) {
$dict[$prop.Name] = $prop.Value
}
foreach ($prop in $Property) {
foreach ($match in $propertyInfo.Match($prop)) {
if ($match.Name -ne $ExpandProperty) {
$dict[$match.Name] = $match.Value
}
}
}
[pscustomobject] $dict
}
$dict.Clear()
}
}
Then you can use it like:
PS ..\> $obj | expand books *
# Title Author Name Age
# ----- ------ ---- ---
# Consider Phlebas Iain M. Banks James 42
# The Player of Games Iain M. Banks James 42
# The Three Body Problem Cixin Liu Susan 36
# The Dark Forest Cixin Liu Susan 36