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

Question

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.

Grok42 · Accepted Answer · 2022-11-22T16:18:18.510

2

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

edited Nov 22 '22 at 16:18

answered Nov 22 '22 at 14:21

Grok42

196
4

Worked like a charm! Ty. I figured id have to insert "blank" values for ids that didnt exist but wasnt sure how to go about. Are you able to explain more clearly the 0..12 foreach loop? I get "what" its doing but i've got a feeling im gonna have to do this for other assets. I just dont want to copy/paste each time. Thanks for your big help! – sdcrytek Nov 22 '22 at 16:48
"0..12" | ForEach-Object iterates 0 to 12 and pushes them to your array element references. For example $Attr[0]; $IDs[0]...$Attr[12]; $IDs[12]. Since you had 13 tags and 13 IDs as long as they are in the position to match each other in the two arrays, 0..12 quick shorthand to sequentially go through them. The $( ...code here.. ) is a Subexpression operator $( ). This was needed to have a second Foreach-Object section, to prevent issues using the $_ operator. Anything explicitly expressed within the $() operator, like $MyObject (at the end) gets passed back pipe to $TradePhones var. – Grok42 Nov 22 '22 at 17:07
Ran out of room... Link below has Subexpression operator & Range operator (0..12) https://learn.microsoft.com/en-us/powershell/module/microsoft.powershell.core/about/about_operators?view=powershell-7.3 – Grok42 Nov 22 '22 at 17:14
Thank you very much! Lifesaver – sdcrytek Nov 22 '22 at 17:26
One thing to consider is here... $( ($Attributes | Where-Object { $_.objectTypeAttributeId -eq $Id} ).objectAttributeValues.displayValue If for some reason your API Rest object has two or more attributes with the same ID# (say two ID# 444) it will probably break it and return an array rather than a single DisplayValue. Unless you put in a | Select-Object -first or something. If each ID will only have one value should be good. – Grok42 Nov 22 '22 at 17:33
Yeah, thankfully, each "ID" has a reference back to an actual "Key" in objectEntries under another object thats not attributes. So we dont have duplicates. – sdcrytek Nov 22 '22 at 18:01

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

1 Answers1