Best way to assign data in hashtable where data is missing for some objects but not others-CodePudding

I am using Powershell 7 to invoke-RestMethod on a JIRA insight API server version (non-cloud). While my invoke rest works like a charm, I am unsure how to approach this as each object's attributes are dynamic based on user input from the front end GUI. My ultimate goal is to take this data from the API, parse it correctly, and then insert it into a SQL table to consume. My first thought was to loop through the data, create a hash table, and statically assign my column headsets as the data but I ran into the issue some data is missing due to an incomplete asset where fields arent assigned.

My Invoke

$TradingPhones = Invoke-RestMethod @TradePhoneParams

This is me drilling down into an object and the results of 2 different objects (I've cleaned up the data to be more readable and for confidential reasons)

$TradingPhones.objectEntries[1].attributes | select objectTypeAttributeID, objectAttributeValues

objectTypeAttributeId objectAttributeValues
--------------------- ---------------------
                  120 {@{value=AssetKey1; displayValue=AssetKey1}}
                  121 {@{value=Tradephone1; displayValue=Tradephone1}}
                  122 {@{value=4/17/2019 8:55:35 PM; displayValue=2019/04/17 16:55}}
                  123 {@{value=10/18/2021 1:58:15 PM; displayValue=2021/10/18 09:58}}
                  124 {@{value=MacAddr1; displayValue=macAddr1}}
                  125 {@{status=; displayValue=In Use}}
                  127 {@{value=SerialNum1; displayValue=SerialNum1}}
                  128 {@{value=Description1; displayValue=Description1}}
                  214 {@{referencedObject=; displayValue=UserLocation1}}
                  215 {@{referencedObject=; displayValue=CostCenter1}}
                  443 {@{user=;  displayValue=User1}}
                  444 {@{referencedObject=; displayValue=TeamName1}}
                  424 {@{referencedObject=; displayValue=Model1}}

Heres another data sample (again cleaned up for confidential reasons)

$TradingPhones.objectEntries[730].attributes | select objectTypeAttributeID, objectAttributeValues

objectTypeAttributeId objectAttributeValues

------------------------------------------

                  120 {@{value=AssetKey2; displayValue=AssetKey2}}
                  121 {@{value=TradePhone2; displayValue=TradePhone2}}
                  122 {@{value=4/27/2022 12:55:15 PM; displayValue=2022/04/27 08:55}}
                  123 {@{value=4/27/2022 12:55:15 PM; displayValue=2022/04/27 08:55}}
                  124 {@{value=MacAddr2; displayValue=MacAddr2}}
                  125 {@{status=; displayValue=In Use}}
                  127 {@{value=SerialNum2; displayValue=SerialNum2}}
                  128 {@{value=Description2; displayValue=Description2}}
                  214 {@{referencedObject=; displayValue=UserLocation2}}
                  444 {@{referencedObject=; displayValue=Team2}}
                  424 {@{referencedObject=; displayValue=Model2}}

As you can see, some fields are missing in the second for of data. objectTypeAttributeId 215, 443 to be exact.

So I havent exactly "tried" to correct this as I am stumped on how to even approach this. The only thing I've tried was this and thats how I found out this happened, and that the data doesn't exist if it's not entered on the frontend.

foreach($item in $TradingPhones.objectEntries)
{
    $TradePhones  = [ordered]@{
        Key = if ($item.attributes.objectTypeAttributeId -eq '120') {$item.attributes.objectAttributeValues.displayValue[0]} else {''}
        Name = if ($item.attributes.objectTypeAttributeId -eq '121') {$item.attributes.objectAttributeValues.displayValue[1]} else {''}
        Created = if ($item.attributes.objectTypeAttributeId -eq '122') {$item.attributes.objectAttributeValues.displayValue[2]} else {''}
        Updated = if ($item.attributes.objectTypeAttributeId -eq '123') {$item.attributes.objectAttributeValues.displayValue[3]} else {''}
        AssetTag = if ($item.attributes.objectTypeAttributeId -eq'124') {$item.attributes.objectAttributeValues.displayValue[4]} else {''}
        Status = if ($item.attributes.objectTypeAttributeId -eq '125') {$item.attributes.objectAttributeValues.displayValue[5]} else {''}
        SerialNumber = if ($item.attributes.objectTypeAttributeId -eq '127') {$item.attributes.objectAttributeValues.displayValue[6]} else {''}
        Description = if ($item.attributes.objectTypeAttributeId -eq '128') {$item.attributes.objectAttributeValues.displayValue[7]} else {''}
        Location = if ($item.attributes.objectTypeAttributeId -eq '214') {$item.attributes.objectAttributeValues.displayValue[8]} else {''}
        CostCenter = if ($item.attributes.objectTypeAttributeId -eq '215') {$item.attributes.objectAttributeValues.displayValue[9]} else {''}
        AssociatedPerson = if ($item.attributes.objectTypeAttributeId -eq '443') {$item.attributes.objectAttributeValues.displayValue[10]} else {''}
        AdminWorkgroup = if ($item.attributes.objectTypeAttributeId -eq '444') {$item.attributes.objectAttributeValues.displayValue[11]} else {''}
        Model = if ($item.attributes.objectTypeAttributeId -eq '424') {$item.attributes.objectAttributeValues.displayValue[12]} else {''}}
}
$TradePhones= $TradePhones.ForEach({$_.ForEach({[PSCustomObject]$_})})

So as you can see, I'm statically assigning these key value pairs which wont work if certain Ids are missing. Its probably something fundamental but I've been staring at this for quite a while and cant figure it out. If it were already a hash table, I could just loop through and assign key value pairs, but since its a PSCustomobject from invoke-restmethod, I am not sure how to.

CodePudding user response：

Something like this may work since you have 13 tags and 13 ID#'s that match one-for-one. Basically create a blank PSCustomObject then add properties to it. The Where-Objects that fail will show return nothing("") so if using SQL may need to change to NULL when you push it to your flavor of SQL.

$TradePhones = $TradingPhones.objectEntries | ForEach-Object {
        $MyObject = [PSCustomObject]@{}
        $Attr = @('Key','Name','Created','Updated','AssetTag','Status','SerialNumber','Description','Location','CostCenter','AssociatedPerson','AdminWorkgroup','Model') 
        $IDs = @(120,121,122,123,124,125,127,128,214,215,443,444,424)
        $Attributes = $_.attributes
        $(
            0..12 | ForEach-Object {
                $Id = $IDs[$_]
                $MyObject | Add-Member -NotePropertyName $Attr[$_] -NotePropertyValue $(($Attributes | Where-Object { $_.objectTypeAttributeId -eq $Id}).objectAttributeValues.displayValue)
            }
            $MyObject
        )
}

Edit: Missed the .objectAttributeValues.displayValue