Powershell - flatten out array with multiple values in multiple subarrays

Question

When I run the API call below, I get the following result as a single line array.

service                   : Test-Change
name                      : svrtest
resource                  : server:domain:us-north-1:123456789:db:test
clusterID                 : N/A
AccountId                 : 12345678899000
tagMappings               : {@{tag=tag1; tagName=Server_Name; Value=SeverSteve}, @{tag=tag2; tagName=Domain; Value=MyCompany}, @{tag=tag3; tagName=ServerID; Value=1234}, @{tag=tag4; tagName=Owner; [email protected]}}
serverrecommendations     : {@{action=reduce; Order=1; Type=OS; instanceFamily=F15; cpuCapacity=64; memoryCapacity=512; emmc=True; audio=False; unitPrice=6.04},
                             @{action=reduce; Order=2; Type=OS; instanceFamily=F16; cpuCapacity=32; memoryCapacity=384; emmc=True; audio=False; unitPrice=5.50}}
storageRecommendations    : {@{action=reduce; Order=1; storage=SAN; storagePrice=0.01; savings=492.35; storageCapacity=2000},
                             @{action=reduce; Order=2; storage=SAN; storagePrice=0.05; savings=277.62; storageCapacity=5000}}

With this result, I am trying to flatten it out to a single row for each recommencation, in this case 2 rows since there are 2 separate recommendations to load into a SQL table.
However, when I loop through the array, I am getting a total of 5 rows.
One row for the items service - tagmappings. 2 for each item in the subarray serverrecommendations. 2 for each item in the subarray storageRecommendations.

$url = "https://call.api.com/run-get-data"
$response = Invoke-RestMethod -Uri $url -Method GET -Headers $headers
$source_array = $response.result

$combinedarray = @()

    foreach ($subArray in $source_array) {
    $Subitem = $subArray | Select-Object service,
    name,
    resource,
    clusterID,
    AccountId,
    @{Label='Server_Name'; Expression={($subArray.tagMappings| where {$_.tagname -eq 'Server_Name'}).Value}},
    @{Label='Domain'; Expression={($subArray.tagMappings| where {$_.tagname -eq 'Domain'}).Value}},
    @{Label='ServerID'; Expression={($subArray.tagMappings| where {$_.tagname -eq 'ServerID'}).Value}},
    @{Label='Owner'; Expression={($subArray.tagMappings| where {$_.tagname -eq 'Owner'}).Value}}
    
    $combinedArray += $Subitem
  
foreach ($svrrecommendation in $subArray.serverrecommendations ) {
    $svrrecommendationitem = $svrrecommendation | Select-Object 'holder1',
    @{Label='Recommended_action'; Expression={$svrrecommendation.action}},
    @{Label='Recommended_order'; Expression={$svrrecommendation.order}},
    @{Label='Recommended_type'; Expression={$svrrecommendation.type}},
    @{Label='Recommended_instnacefamily'; Expression={$svrrecommendation.instnacefamily}},
    @{Label='Recommended_cpucapacity'; Expression={$svrrecommendation.cpucapacity}},
    @{Label='Recommended_memorycapacity'; Expression={$svrrecommendation.memorycapacity}},
    @{Label='Recommended_emmc'; Expression={$svrrecommendation.emmc}},
    @{Label='Recommended_audio'; Expression={$svrrecommendation.audio}},
    @{Label='Recommended_unitprice'; Expression={$svrrecommendation.unitprice}}
    
    $combinedArray += $svrrecommendationitem
    }

foreach ($storage in $subArray.storagerecommendations) {
    $storageitem = $storage | Select-Object 'holder2',      
    @{Label='Recommended_Storage_action'; Expression={$storage.action}},
    @{Label='Recommended_Storage_order'; Expression={$storage.order}},
    @{Label='Recommended_Storage_storage'; Expression={$storage.storage}},
    @{Label='Recommended_Storage_storagePrice'; Expression={$storage.storagePrice}},
    @{Label='Recommended_Storage_savings'; Expression={$storage.savings}},
    @{Label='Recommended_Storage_storageCapacity'; Expression={$storage.storageCapacity}}

    $combinedArray += $storageitem
    }

}

foreach ($line in $combinedArray)
{
    $service = $line.service
    $name = $line.name
    $resource  = $line.resource
    $clusterID  = $line.clusterID
    $AccountId = $line.AccountId
    $Server_Name = $line.Server_Name,
    $Domain = $line.Domain,
    $ServerID = $line.ServerID,
    $Owner = $line.Owner,
    $Recommended_action line.Recommended_action,
    $Recommended_order = line.Recommended_order,
    $Recommended_type = line.Recommended_type,
    $Recommended_instnacefamily = line.Recommended_instnacefamily,
    $Recommended_cpucapacity = line.Recommended_cpucapacity,
    $Recommended_memorycapacity = line.Recommended_memorycapacity ,
    $Recommended_emmc = line.Recommended_emmc,
    $Recommended_audio = line.Recommended_audio,
    $Recommended_unitprice = line.Recommended_unitprice,
    $Recommended_Storage_action = line.Recommended_Storage_action,
    $Recommended_Storage_order = line.Recommended_Storage_order,
    $Recommended_Storage_storage = line.Recommended_Storage_storage,
    $Recommended_Storage_storagePrice = line.,
    $Recommended_Storage_savings = line.Recommended_Storage_storagePrice,
    $Recommended_Storage_storageCapacity = line.Recommended_Storage_storageCapacity 
    
    $SQLInsert = "INSERT INTO [TESTDB].[SVR_SAVINGS].[HUGE_SVRS]
    (
        [service]
        ,[name]
        ,[resource]
        ,[clusterID]
        ,[AccountId]
        ,[Server_Name]
        ,[Domain]
        ,[ServerID]
        ,[Owner]
        ,[Resource_Name]
        ,[Power_Strategy_Previously_Shutdown]
        ,[Recommended_action]
        ,[Recommended_order]
        ,[Recommended_type]
        ,[Recommended_instnacefamily]
        ,[Recommended_cpucapacity]
        ,[Recommended_memorycapacity]
        ,[Recommended_emmc]
        ,[Recommended_audio]
        ,[Recommended_unitprice]
        ,[Recommended_Storage_action]
        ,[Recommended_Storage_order]
        ,[Recommended_Storage_storage]
        ,[Recommended_Storage_storagePrice]
        ,[Recommended_Storage_savings]
        ,[Recommended_Storage_storageCapacity]
        
        )
    Values (
    
        '$service'
        ,'$name'
        ,'$resource'
        ,'$clusterID'
        ,'$AccountId'
        ,'$Server_Name'
        ,'$Domain'
        ,'$ServerID'
        ,'$Owner'
        ,'$Resource_Name'
        ,'$Power_Strategy_Previously_Shutdown'
        ,'$Recommended_action'
        ,'$Recommended_order'
        ,'$Recommended_type'
        ,'$Recommended_instnacefamily'
        ,'$Recommended_cpucapacity'
        ,'$Recommended_memorycapacity'
        ,'$Recommended_emmc'
        ,'$Recommended_audio'
        ,'$Recommended_unitprice'
        ,'$Recommended_Storage_action'
        ,'$Recommended_Storage_order'
        ,'$Recommended_Storage_storage'
        ,'$Recommended_Storage_storagePrice'
        ,'$Recommended_Storage_savings'
        ,'$Recommended_Storage_storageCapacity'
    )"

    try {

        Invoke-SQLQuery -Query $SQLInsert -ServerInstance $SQLInstance

    }
    catch {
        $err = $Error[0].Exception;
        Write-host $err 
        break
    }
}

I have tried this method (Flatten out multiple PowerShell arrays) and it did not work.

Any suggestions are appreciated!!

Please try to reduce your question to a minimal reproducible example or as close as you can get to one, and include expected vs. actual output. — mklement0
– mklement0, Commented Jul 23, 2024 at 22:54
As an aside: Extending arrays in a loop with += is inefficient, because a new array must be created behind the scenes in every iteration, given that arrays are of fixed size; a much more efficient approach is to use a foreach loop as an expression and let PowerShell itself collect the outputs in an array: [array] $outputs = foreach (...) { ... } - see this answer. In case you need to create arrays manually, e.g. to create multiple ones, use an efficiently extensible list type - see here. — mklement0
– mklement0, Commented Jul 23, 2024 at 22:54
It might help to add the object-graph to the question: $response.result | ConvertTo-Json (or ConvertTo-Expression). The later (custom) cmdlet is part of the ObjectGraphTools which might help you to better understand the given object-graph and e.g. the path to each individual node: $response.result | Get-ChildNode -Recurse. — iRon
– iRon, Commented Jul 24, 2024 at 7:54
To flatten your object-graph using this tool set, you might do: $FlatObject = [Ordered]@{}; $response.result | Get-ChildNode -Recurse -Leaf | ForEach-Object { $FlatObject["$($_.Path)"] = $_.Value }; $FlatObject — iRon
– iRon, Commented Jul 24, 2024 at 7:54

y y · Accepted Answer · 2024-07-24 19:28:23Z

foreach ($subArray in $source_array) {

    $hash = [Ordered]@{}

    $hash.service = $subArray.service
    $hash.name = $subArray.name
    $hash.resource = $subArray.resource
    $hash.clusterID = $subArray.clusterID
    $hash.AccountId = $subArray.AccountId

    foreach ($tag in $subArray.tagMappings) {
        $hash[$tag.tagName] = $tag.Value
    }

    foreach (  $i in 0..( @($subArray.serverrecommendations).Count - 1 )  ) {
        foreach ( $property in $subArray.serverrecommendations[$i].PSObject.Properties.Name ) {
            $hash["Recommended_$property"] = $subArray.serverrecommendations[$i].$property
        }
        foreach ( $property in $subArray.storageRecommendations[$i].PSObject.Properties.Name ) {
            $hash["Recommended_Storage_$property"] = $subArray.storageRecommendations[$i].$property
        }
        Write-Output -InputObject ([PSCustomObject]$hash)
    }
}

This is based on the assumptions that:

'tagMappings' and 'serverrecommendations' and 'storageRecommendations' are an array of objects with properties and values
There is an equal amount of 'serverrecommendations' and 'storageRecommendations' objects
all 'serverrecommendations' and 'storageRecommendations' objects have the same property names.

Collectives™ on Stack Overflow

Powershell - flatten out array with multiple values in multiple subarrays

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related