Handling Nested JSON returned from Invoke-RestMethod

Question

I'm trying pull data from WhatConverts via their API, and to then upload that into a SQL table. As I've dug deeper into the nature of the underlying data, there are several data types being returned as objects; I only know upfront the outer data element type will be an object, but the nested element names, and types in the nested object array are totally variable. The returned data is json.

My plan was to import the typed data into SQL, and for the nested data, to try and convert the nesting into XML and import that as XML data into SQL for possible future processing based on the end user's requirements. I'm grooming this into a DataTable from the Invoke-RestMethod object prior to my SQL import. I can't handle the nested data, here is where I'm at:

#NOTE $importedData is an array of PSCustomObject returned from Invoke-RestMethod
$manipulatedData = New-Object System.Data.DataTable
$importedData | ForEach-Object {
    $newRow = $manipulatedData.NewRow()
    foreach ($prop in $_.PSObject.Properties) {
        # nested object
        if ($prop.Value -is [System.Management.Automation.PSCustomObject]) { 
            $tempPropname = $prop.Name
            if (-not ($manipulatedData.Columns.Contains($tempPropname))) {
                $newCol = New-Object System.Data.DataColumn($tempPropname)
                $manipulatedData.Columns.Add($newCol, "System.String" ) | Out-Null
            }
            #$prop | Select-Object Name,Value | ConvertTo-Xml -As String
            $newRow.$tempPropname = $prop | Select-Object Name,Value | ConvertTo-Xml -As String
        }
        # primitive value
        else {
            $tempPropname = $prop.Name 
            if (-not ($manipulatedData.Columns.Contains(($tempPropname)))) {
                $newCol = New-Object System.Data.DataColumn($tempPropname)
                $manipulatedData.Columns.Add($newCol, $prop.TypeNameOfValue ) | Out-Null
            }
            if ($prop.Value -ne '{}') {
                $newRow.$tempPropname = $prop.Value
            }
        }
    }
    $manipulatedData.Rows.Add($newRow)
}

For this exercise I believe I need to iterate through each of the nested property elements (I only care about 1 level deep), but then I got stuck having to build out an XML document from scratch so I changed direction and tried just exporting the whole object to XML.

The results are garbage, here is the output:

SetValueInvocationException: 
Line |
  90 |              $newRow.$tempPropname = $prop | Select-Object Name,Value  …
     |              ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
     | Exception setting "additional_fields": "Type of value has a mismatch with column typeCouldn't store <<?xml version="1.0" encoding="utf-8"?> <Objects>   <Object Type="System.Management.Automation.PSCustomObject">     <Property Name="Name"
     | Type="System.String">additional_fields</Property>     <Property Name="Value" Type="System.Management.Automation.PSCustomObject">       <Property Type="System.String">@{Junk?=No; New Client?=Not Set; Service-Based?=Not Set}</Property>       <Property Name="Junk?"
     | Type="System.Management.Automation.PSNoteProperty">No</Property>       <Property Name="New Client?" Type="System.Management.Automation.PSNoteProperty">Not Set</Property>       <Property Name="Service-Based?" Type="System.Management.Automation.PSNoteProperty">Not
     | Set</Property>     </Property>   </Object> </Objects>> in additional_fields Column.  Expected type is Object[]."

Answer 1

Your symptom implies that your input JSON is irregularly formed: one of the columns was initially created from an array based on the first input object, whereas the value for the same column (property) in a subsequent input object happened to be a single [pscustomobject] instance.

Here's example JSON that reproduces your problem:

[
  {
    "Primitive": 42,
    "Array": [ "foo", "bar" ]
  },
  {
    "Primitive": 43,
    "Array": {
      "CustomObjectScalar": "foo"
    }
  }
]

With the above input, the column for property Array is initially created as [object[]] based on the first input object (i.e. as an array whose elements aren't type-constraint, which is what ConvertFrom-Json converts array-valued JSON properties to), and when an attempt is made to assign a [string] instance as the column value for row created for the second input object, the type mismatch error you saw occurs.

---

Here is one way to fix the problem, within the following constraints:

If a [pscustomobject] instance is encountered and the column data type is:

• [object[]], simply wrap the XML string representation of the instance in a single-element array, which avoids the type mismatch.

• [string], assign the XML string representation as-is, as before.

• otherwise, throw (report a script-terminating error)

Look for # !! comments in the code below.

#NOTE $importedData is an array of PSCustomObject returned from Invoke-RestMethod
$manipulatedData = New-Object System.Data.DataTable
$importedData | ForEach-Object {
  $newRow = $manipulatedData.NewRow()
  foreach ($prop in $_.PSObject.Properties) {
    # nested object
    if ($prop.Value -is [System.Management.Automation.PSCustomObject]) { 
      $tempPropname = $prop.Name
      # !! Determine the value to store up front.
      # !! See notes re CLIXML in the bottom section.
      $value = $prop | Select-Object Name, Value | ConvertTo-Xml -As String
      if (-not ($manipulatedData.Columns.Contains($tempPropname))) {
        $newCol = New-Object System.Data.DataColumn($tempPropname)
        $manipulatedData.Columns.Add($newCol, 'System.String' ) | Out-Null
        $dataType = [string] # !! Store the assigned type.
      }
      else {
        # !! Determine the preexisting data type.
        $dataType = $manipulatedData.Columns[$tempPropname].DataType
      }
      # !! Assign a column value based on the column's data type.
      switch ($dataType) {
        ([Object[]]) {
          $newRow.$tempPropname = @($value) # !! Wrap in single-element array
        }
        ([string]) {
          $newRow.$tempPropname = $value # !! Assign a string as-is, as before.
        }
        default {
          # !! Report an error, if the column has any other data type.
          throw "Unexpected preexisting data type of column ${tempPropertyName}: [$dataType]"
        }
      }
    }
    # primitive value
    else {
      $tempPropname = $prop.Name 
      if (-not ($manipulatedData.Columns.Contains(($tempPropname)))) {
        $newCol = New-Object System.Data.DataColumn($tempPropname)
        $manipulatedData.Columns.Add($newCol, $prop.TypeNameOfValue ) | Out-Null
      }
      if ($null -ne $prop.Value) {
        $newRow.$tempPropname = $prop.Value
      }
    }
  }
  $manipulatedData.Rows.Add($newRow)

}

---

As an aside:

• You're better off storing the data in CLIXML format, which ConvertTo-Xml does not produce. In fact, this cmdlet is rarely useful - see this answer.

• Unlike what ConvertTo-Xml outputs, CLIXML can be reconverted to objects later (albeit with limited type fidelity - see this answer - but with [pscustomobject] graphs converted from JSON these limitations do not come into play.

• In Windows PowerShell and PowerShell (Core) 7 up to v7.4.x, you must use Export-Clixml in order to produce CLIXML output, which unfortunately requires output to a file (which can later be reimported with Import-Clixml); thus, you'll need to use a temporary file for your use case.

  • PowerShell 7.5+^[1] now offers in-memory, string-based alternatives,
    ConvertTo-CliXml and ConvertFrom-CliXml

---

^{[1] Available since preview 4 of v7.5 (v7.5.0-preview.4)}