Array of hashtables does not get converted to JSON by ConvertTo-Json

Question

Been working this for an hour now and can't get to the bottom of it. The following code builds an array of hashtables, adds that array into another hashtable along with a bunch of other key-value pairs, then converts the whole lot to JSON:

$01_ch_channel_hist_id = @{"ColumnName"="01_ch_channel_hist_id";"ColumnType"="bigint"}
$02_ch_channel_id = @{"ColumnName"="02_ch_channel_id";"ColumnType"="bigint"}
$08_ch_load_id = @{"ColumnName"="08_ch_load_id";"ColumnType"="bigint"}
$columns = $01_ch_channel_hist_id,$02_ch_channel_id,$08_ch_load_id
$ChannelDimH = @{}
$ChannelDimH.Add("Columns",$columns)
$ChannelDimH.Add("TableName","CHANNEL_DIM_H")
$ChannelDimH.Add("UniqueColumn","channel_id")
$tables = @()
$tables += $ChannelDimH
$sqoopOracleTableAndColumnMetadata = @{}
$sqoopOracleTableAndColumnMetadata.Add("tables",$tables)

$sqoopOracleTableAndColumnMetadata | ConvertTo-Json

Here is the output:

{
    "tables":  [
                   {
                       "TableName":  "CHANNEL_DIM_H",
                       "UniqueColumn":  "channel_id",
                       "Columns":  "System.Collections.Hashtable System.Collections.Hashtable System.Collections.Hashtable"
                   }
               ]
}

Notice that the hastables inside the "Columns" array get returned as:

"System.Collections.Hashtable System.Collections.Hashtable System.Collections.Hashtable"

The data in those hashtables isn't appearing in my JSON document. Why not?

ah I've figured it out. its the Depth parameter of ConvertTo-Json.: $sqoopOracleTableAndColumnMetadata | ConvertTo-Json -Depth 10 — jamiet, Nov 04 '15 at 22:05
Glad to hear you figured it out, i was just about to suggest the `Depth` parameter as i had the same exact issue a couple of months ago. You should post your findings as an answer and mark it as the answer for anyone else looking at the question for a solution. :) — Bluecakes, Nov 04 '15 at 22:08

score 2 · Answer 1 · answered Nov 04 '15 at 22:19

Figured it out. Call to ConvertTo-Json needed a depth specifying

$01_ch_channel_hist_id = @{"ColumnName"="01_ch_channel_hist_id";"ColumnType"="bigint"}
$02_ch_channel_id = @{"ColumnName"="02_ch_channel_id";"ColumnType"="bigint"}
$08_ch_load_id = @{"ColumnName"="08_ch_load_id";"ColumnType"="bigint"}
$columns = $01_ch_channel_hist_id,$02_ch_channel_id,$08_ch_load_id
$ChannelDimH = @{}
$ChannelDimH.Add("Columns",$columns)
$ChannelDimH.Add("TableName","CHANNEL_DIM_H")
$ChannelDimH.Add("UniqueColumn","channel_id")
$tables = @()
$tables += $ChannelDimH
$sqoopOracleTableAndColumnMetadata = @{"tables"=$tables}
#$sqoopOracleTableAndColumnMetadata.Add("tables",$tables)

$sqoopOracleTableAndColumnMetadata | ConvertTo-Json -Depth 4

Array of hashtables does not get converted to JSON by ConvertTo-Json

1 Answers1