How to convert nested JSON into hashtable in powershell?

Question

I'm trying to convert JSON into hashtable in 5.1 version of Powershell. But the output is coming as an object again for FieldMapping Key. Can we get the key value pairs for FieldMapping key?

We have ConvertFrom-Json -AsHashtable in 7.1 version . Ideally trying to get the same o/p in 5.1 as well. What can I try next?

My json:

$json = '[
 
                              {
 
                                  "EntityType": "IP",
 
                                  "FieldMapping":  [
 
                                                       {
 
                                                           "ColumnName":  "FileHashCustomEntity"
                                                            "Identifier":  "Address"
                                                           
 
                                                       }
 
                                                   ]
 
                              }
 
                          ]'

My code:

$entities = $json | ConvertFrom-Json 
$ht2 = @{}
$hash = $entities.psobject.properties | Foreach { $ht2[$_.Name] = $_.Value }
echo $ht2

My output:

Key   : EntityType
Value : IP
Name  : EntityType


Key   : FieldMapping
Value : {@{ColumnName=FileHashCustomEntity; Identifier=Address}}
Name  : FieldMapping

Expected output:

 Key   : EntityType
 Value : IP
 Name  : EntityType


 Key   : FieldMapping
 Value : {FileHashCustomEntity}
 Name  : FieldMapping

Hi jdweng.. tried using the above suggested code. But got o/p in this format: Key : EntityType Value : Microsoft.PowerShell.Commands.Internal.Format.FormatEntryData Name : EntityType Key : FieldMapping Value : {Microsoft.PowerShell.Commands.Internal.Format.FormatStartData, Microsoft.PowerShell.Commands.Internal.Format.GroupStartData, Microsoft.PowerShell.Commands.Internal.Format.FormatEntryData, Microsoft.PowerShell.Commands.Internal.Format.GroupEndData…} Name : FieldMapping — dev333, Jan 20 '23 at 10:22
You need to do this recursively and treat arrays specifically. If this works for you, we can close as duplicate: https://stackoverflow.com/a/34383413/7571258 — zett42, Jan 20 '23 at 10:22

score 1 · Answer 1 · answered Jan 20 '23 at 12:43

1

Right now you're ending up with a top-level hashtable containing custom objects as entries - what you need to do is convert every object in the resulting object hierarchy recursively.

Here's a simple function that does that:

function Convert-PSCToHashTable
{
  param(
    $InputObject, 
    [int]$Depth = 5
  )

  if($Depth -gt 0){
    if($InputObject -is [System.Collections.IList]){
      return @($InputObject |ForEach-Object {Convert-PSCToHashTable $_ -Depth ($Depth - 1)})
    }

    if($InputObject.psobject.BaseObject -is [System.Management.Automation.PSCustomObject]){
      $ht = @{}
      foreach($prop in $InputObject.psobject.Properties){
        $ht[$prop.Name] = Convert-PSCToHashTable $prop.Value -Depth ($Depth - 1)
      }
      return $ht
    }
  }

  return $InputObject
}

Now you can do:

$object = ConvertFrom-Json $json
$hashtable = Convert-PSCToHashTable $object

$hashtable['FieldMapping']['ColumnName'] # "FileHashCustomEntity"

answered Jan 20 '23 at 12:43

Mathias R. Jessen

157,619
12
148
206

tried to use the above function to convert json to hashtable. I'm trying to send this hashtable to the following command. New-AzSentinelAlertRule -ResourceGroupName $ResourceGroupName -WorkspaceName $WorkspaceName -QueryFrequency 1:00:00 -QueryPeriod 1:00:00 -DisplayName "TesS-2" -Kind Scheduled -Query "SecurityAlert | where TimeGenerated == '99'" -Severity Low -TriggerOperator GreaterThan -TriggerThreshold 10 -EntityMapping $hashtable – dev333 Feb 02 '23 at 10:44
Facing this error : Invalid data model. [: Invalid length of '0' for 'EntityMappings'. 'EntityMappings' length should be between '1' and '5'] – dev333 Feb 02 '23 at 10:44
How can I send this hashtable to EntityMapping parameter ? Can you please help me out ? – dev333 Feb 02 '23 at 10:45

score 0 · Answer 2 · answered Jan 20 '23 at 11:10

This works. Included debug statements so you can see how the data is organized

$json = @" 
[
 
                              {
 
                                  "EntityType": "IP",
 
                                  "FieldMapping":  [
 
                                                       {
 
                                                           "ColumnName":  "FileHashCustomEntity",
                                                            "Identifier":  "Address"
                                                           
 
                                                       }
 
                                                   ]
 
                              }
 
                          ]
"@
$entities = $json | ConvertFrom-Json 
$entities
$ht2 = [System.Collections.ArrayList]::new()
$newRow = New-Object -TypeName psobject
$newRow | Add-Member -NotePropertyName EntityType -NotePropertyValue $entities.EntityType
foreach($property in $entities.FieldMapping)
{
$property | Format-Table
   $newRow | Add-Member -NotePropertyName ColumnName -NotePropertyValue $property.ColumnName  
   $newRow | Add-Member -NotePropertyName Identifier -NotePropertyValue $property.Identifier
 
}
$ht2.Add($newRow)  | Out-Null

$ht2 | Format-Table

This outputs an ArrayList of PsCustomObjects. Not a Hashtable at all as the OP wants — Theo, Jan 20 '23 at 13:08

How to convert nested JSON into hashtable in powershell?

2 Answers2