I'm using the Invoke-RestMethod to get the data from REST API. The response is an array of strings and hash tables. I need to save the response into a CSV file. I know that I can use explicit select-object. This is what I have now:

$response = Invoke-RestMethod -Method Get -Uri $url -Headers $requestHeaders
$response | select-object `
@{Name="HashTable1_Column1"; Expression={ $_.HashTable1.Column1}},
@{Name="HashTable1_Column2"; Expression={ $_.HashTable1.Column2}},
NormalStringColumn1,
NormalStringColumn2,
@{Name="HashTable2_Column1"; Expression={ $_.HashTable2.Column1}},
@{Name="HashTable2_Column2"; Expression={ $_.HashTable2.Column2}}, | Export-Csv $filePath -NoTypeInformation -Encoding utf8 -Delimiter ";"

but is there any way how to expand all hash tables dynamically without the need of typing all fields and save the response with all expanded properties if I don't know the structure of the response?

Thanks a lot

CodePudding user response：

Use reflection to inspect the [pscustomobject] instances returned from your Invoke-RestMethod call, using the intrinsic .psobject member's .Properties collection, which allows you to programmatically examine any object's properties.

Note that the following solution is limited to one level of nesting, suitable for handling your sample data. Deeper nesting would require a recursive solution (though representing more deeply nested objects in a CSV could get impractical).

# Simulate an Invoke-RestMethod call
# by parsing a JSON string literal.
$fromJson = ConvertFrom-Json @'
[
  {
    "HistoricalProperties": {
      "PropertyName": "Name",
      "IsProductive": "true",
      "time": "09:00-17:00",
      "Id": "id",
      "Sum": "8.0000"
    },
    "Date": "2021-01-25T00:00:00",
    "Email": "email",
    "EmployeeId": "id",
    "Number": 1,
    "ActualProperties": {
      "PropertyName": "Name",
      "IsProductive": "true",
      "time": "09:00-17:00",
      "Id": "id",
      "Sum": "9.0000"
    }
  }
]
'@

# Loop over all [pscustomobject] instances that the JSON objects
# inside the array were parsed into.
# Note: The assumption is that if there are multiple instances,
#       they all have the same internal structure (properties).
$fromJson | ForEach-Object {
  # Convert the object at hand to a flat representation,
  # using an aux. ordered hashtable.
  $oht = [ordered] @{}
  foreach ($prop in $fromJson.psobject.Properties) {
    if ($prop.Value -is [pscustomobject]) { # nested object
      foreach ($nestedProp in $prop.Value.psobject.Properties) {
        $oht[($prop.Name   '_'   $nestedProp.Name)] = $nestedProp.Value
      }
    }
    else { # primitive value, such as a string
      $oht[$prop.Name] = $prop.Value
    }
  }
  # Output the hashtable as a [pscustomobject].
  # Note: No longer required in PowerShell (Core) 7 
  [pscustomobject] $oht
} | ConvertTo-Csv -NoTypeInformation -Delimiter ';'

The above outputs the following:

"HistoricalProperties_PropertyName";"HistoricalProperties_IsProductive";"HistoricalProperties_time";"HistoricalProperties_Id";"HistoricalProperties_Sum";"Date";"Email";"EmployeeId";"Number";"ActualProperties_PropertyName";"ActualProperties_IsProductive";"ActualProperties_time";"ActualProperties_Id";"ActualProperties_Sum"
"Name";"true";"09:00-17:00";"id";"8.0000";"1/25/2021 12:00:00 AM";"email";"id";"1";"Name";"true";"09:00-17:00";"id";"9.0000"

Note:

• To save the CSV to a file, use Export-Csv instead of ConvertTo-Csv. Be sure to specify an encoding, such as -Encoding utf8, because in Windows PowerShell the default is ASCII(!)

• In PowerShell (Core) 7 the solution can be simplified in two ways:

  • You don't need the [pscustomobject] cast before $oht, because ConvertTo-Csv now also accepts (ordered and unordered) hashtables as input.

  • You don't need the -NoTypeInformation switch anymore.

CodePudding user response：

Since you didn't provide any sample XML-data I can't verify that you can use this method to examine the object structure. But try this to get a grip of the structure you'd like to save...

Install-Module PowerShellCookbook

$response = Invoke-RestMethod -Method Get -Uri $url -Headers $requestHeaders

[xml]$response | Show-Object

Edit: Show-Object is really old and I actually had some issues navigating an XML object. Also, when installing it there will be conflicts with these cmdlets already in place: Format-Hex,Send-File,Get-Clipboard,New-SelfSignedCertificate,Send-MailMessage,Set-Clipboard

I had to do Save-Module PowerShellCookbook instead and extract the single function Show-Object from the module file.

Edit 2: To explore the data structure, I concur with the discussion below that piping to Format-Custom is a more suitable option.