Combine API results into single object in Powershell

Question

I have two API endpoints to hit and retrieve user info and write the data into a SQL table. The endpoint uses employee ID in the URL so I am looping through each user to grab their data. Endpoint #2 contains "custom fields" for the user. I am trying to combine the returns of both endpoints and write them as a single row for each user in the SQL table.

They return PSCustomObject as a hash table. Endpoint # 1: cat6 NoteProperty string cat6=NONE

Endpoint #2: CustomText94 NoteProperty System.Management.Automation.PSCustomObject CustomText94=@{description=; value=}

function Export-Feed {

    Begin {
        $serverInstance = ""
        $database = ""
        $tableName = ""
        $employees = Get-Directory | Where-Object eestatus -eq A
    }

    Process {
        $result = $employees | ForEach-Object -Parallel {
            $params = @(
                'firstname',
                'middlename',
                'lastname',
                @{n='ADID';e={(Connect-Api -apiEndpoint "/api/v1/employee/$($_.eecode)/customfield").CustomText04.value}},
                'hire_date',
                'rehire_date',
                'position_title',
                'termination_date',
                'work_email',
                'employee_code',
                'clocksequencenumber',
                'department_code',
                'department_description',
                'employee_status',
                'supervisor_primary',
                'supervisor_primary_code',
                'supervisor_secondary',
                'supervisor_secondary_code',
                'supervisor_tertiary',
                'supervisor_tertiary_code',
                'supervisor_quaternary',
                'cat1',
                'cat1desc',
                'cat2',
                'cat2desc',
                'cat3',
                'cat3desc',
                'cat4',
                'cat4desc',
                'cat5',
                'cat5desc',
                'cat6',
                'cat6desc',
                'cat7',
                'cat7desc',
                'cat8',
                'cat8desc',
                'cat9',
                'cat9desc'
            )


            Connect-Api -apiEndpoint "/api/v1/employee/$($_.eecode)" | Select-Object $params

        }
    }
    
    End {
        $result | Out-File c:\temp\test.txt
        #Write-SqlTableData -DatabaseName $database -TableName $tableName -ServerInstance $serverInstance -SchemaName dbo -InputData $result -force 
    }
}

Answer 1

To merge two custom objects ([pscustomobject] instances, which are not the same as hashtables), use the following technique:

# Two sample input objects.
$o1 = [pscustomobject] @{ one  = 1; two  = 2; three = 3 }
$o2 = [pscustomobject] @{ four = 4; five = 5; six   = 6 }

# Merge the two, by appending the properties of the 2nd to the 1st.
# Note: This assumes that there is no overlap between the property names.
foreach ($prop in $o2.psobject.Properties) {
  $o1.psobject.Properties.Add($prop, $true)
}

# $o1 now contains the union of both property sets; 
# Output it.
$o1 | Format-Table

The above yields:

one two three four five six
--- --- ----- ---- ---- ---
  1   2     3    4    5   6

The above relies on every object in PowerShell having a hidden .psobject property that provides reflection information about the object, notably the collection of all properties returned by the .Properties property.

PowerShell allows properties to be dynamically added to any property, which is feature of its ETS (Extended Type System). [pscustomobject] instances contain only such dynamic properties. The .Add() method allows adding a copy of another object's property to an object; the $true indicates that no validation need be performed, which speeds up the operation.