Using Powershell convert JSON with multiple column headers and rows

Question

I've used this question to get me started. However, I have the following JSON data that has multiple column headers and rows that contain multiple sets of data.

{
    "columnHeaders": {
        "dimensions": [
            "year_month",
            "tag1",
            "computer_name",
            "account_name",
            "resource_name",
            "category3",
            "tag8",
            "tag11",
            "db_engine"
        ],
        "metrics": [
            {
                "name": "usage_amount",
                "dataType": "float"
            },
            {
                "name": "pretax_cost",
                "dataType": "currency"
            },
            {
                "name": "actial_cost",
                "dataType": "currency"
            }
        ]
    },
    "rows": [
        {
            "dimensions": [
                "2022-04",
                "(not set)",
                "server1",
                "account1",
                "server1_bios_name",
                "undefined",
                "(not set)",
                "(not set)",
                "SQLServer"
            ],
            "metrics": [
                {
                    "count": "1",
                    "max": "0.52",
                    "min": "0.10",
                    "sum": "0.52"
                },
                {
                    "count": "1",
                    "max": "-22.24",
                    "min": "-22.24",
                    "sum": "-22.24"
                },
                {
                    "count": "1",
                    "max": "1.26",
                    "min": "0",
                    "sum": "1.52"
                }
            ]
        }
    ]
}

If I use the last suggestion, I can get the dimensions fine, but that I need to the "sum" value for each metric.

$A = Get-Content 'C:\Temp\sample.json' | ConvertFrom-Json 


    $Rows = 
    ForEach($Row in $A.rows )
    {
        $TmpHashCol = [Ordered]@{}
        $TmpHashMet = [Ordered]@{}
       
        For($i = 0; $i -lt $Row.dimensions.Length; ++$i )
        {
            $TmpHashCol.Add($A.columnHeaders.dimensions[$i],$Row.dimensions[$i])

                 
                 #For($s = 0; $s -lt $Row.metrics.length; ++$s )
                 #   {
             $TmpHashMet.Add($A.columnHeaders.metrics.name[$i],$Row.metrics.sum[$i])
             #$TmpHashMet.Add($A.columnHeaders.metrics[$i],$Row.metrics.sum[$i])
            
                  #  }

            
        }

For the '$TmpHashMet' I get the error: Exception calling "Add" with "2" argument(s): "Key cannot be null. Yet, when I try to specify the name it doesn't like that either.

This is what I would like for the data to look like when complete:

year_month      :   2022-04
tag1            :   (not set)
computer_name   :   server1
account_name    :   account1
resource_name   :   server1_bios_name
category3       :   undefined
tag8            :   (not set)
tag11           :   (not set)
db_engine       :   SQLServer
usage_amount    :   0.52
pretax_cost     :   -22.24
actial_cost     :   1.52

Many thanks in advance!!!

Santiago Squarzon · Accepted Answer · 2022-05-31 21:46:39Z

2

This is one way you could do it, you need a new inner loop for the Values on .metrics.sum, I have also modified your code a bit so it works in case the Json comes with more than 1 row.

$headers = $json.columnHeaders.dimensions
$metrics = $json.columnHeaders.metrics.name

foreach($row in $json.rows) {
    $out = [ordered]@{}
    for($i = 0; $i -lt $row.dimensions.Count; $i++) {
        $out[$headers[$i]] = $row.dimensions[$i]
    }
    for($i = 0; $i -lt $metrics.Count; $i++) {
        $out[$metrics[$i]] = $row.metrics.sum[$i]
    }
    [pscustomobject] $out
}

The output should look like your desired one:

year_month    : 2022-04
tag1          : (not set)
computer_name : server1
account_name  : account1
resource_name : server1_bios_name
category3     : undefined
tag8          : (not set)
tag11         : (not set)
db_engine     : SQLServer
usage_amount  : 0.52
pretax_cost   : -22.24
actial_cost   : 1.52

edited May 31, 2022 at 21:46

answered May 31, 2022 at 21:22

Santiago Squarzon

65.5k5 gold badges26 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

mclayton Over a year ago

Small bit of code golf - you can initialise with $metrics = @( $json.columnHeaders.metrics.name ), using Member Enumeration. to get an array of the name properties.

Santiago Squarzon Over a year ago

@mclayton totally, I didn't see the json in details, I thought OP was using column names of his choice. thanks!

Collectives™ on Stack Overflow

Using Powershell convert JSON with multiple column headers and rows

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related