Combining parts of a PSCustomObject

PowerShell Help
1

Hi folks,

I have hit a brick wall with this problem. Hopefully I can explain it properly since I already struck out with google.

I have data that I pulled from an API. The response was JSON but it looked like Powershell automatically converted it to a PSCustomObject. Ok, so far no problem as I can access all the data from the response. The real problem is I need different parts of the object so I can export it to a CSV or possibly directly into SQL but that’s a different problem. Right now I just can’t wrap my head around getting the data the way I need it.

So I have my object stored in $stats.

$stats.start & $stats.end just contain the date/time of the metrics and apply to all samples and would therefore need to be repeated.

$stats.items actually hold the metrics I need.
i.e.

breakdown_1                                     metrics               
-----------			                        -----------------                                                                                                  
@{id=61093; name=Toronto}              {3859851.4375, 236.6875}                          
@{id=80334; name=New York}	       {6214069.317073171, 540.80208333333337}          
@{id=79296; name=Boston}	       {1616149.763888889, 93.805555555555557}

How to I grab all of that data and output into one ‘table’?
Such as:
ID, Name, Start, End, Metric1, Metric2

Thanks,

2

We don’t have a good full set of sample data from your post, but as I understand your question this should do it. If not, it should give you an idea of how to get the data you want.

[PSCustomObject]@{
    Start = "Thursday, October 22, 2015 11:03:06 PM"
    breakdown_1 = @{id=61093; name='Toronto'};
    metrics = 3859851.4375, 236.6875
    End = "Thursday, October 22, 2015 12:03:06 PM"
} | Select-Object Start, End, @{Label = 'id'; Expression = {$_.breakdown_1.id}}, @{Label = 'name'; Expression = {$_.breakdown_1.name}}, @{Label = 'Metric 1'; Expression = {$_.metrics[0]}}, @{Label = 'Metric 2'; Expression = {$_.metrics[1]}}

Results in:
Start : Thursday, October 22, 2015 11:03:06 PM
End : Thursday, October 22, 2015 12:03:06 PM
id : 61093
name : Toronto
Metric 1 : 3859851.4375
Metric 2 : 236.6875

3

Thanks for the response Curtis, I’m sorry I should have posted some better sample data.

Here is a full sample in JSON (sorry, just easier for me to copy)

{  
   "start":"2015-10-19T10:08:05.7639282",
   "end":"2015-10-22T10:08:05.7639282",
   "timezone":{  
      "id":0,
      "name":"ET"
   },
   "summary":{  
      "fields":{  },
      "items":[  
         {  
            "breakdown_1":{  
               "id":61093,
               "name":"Toronto"
            },
            "synthetic_metrics":[  
               3845864.204861111,
               400.14501737223731,
               98.569444444444443,
               236.11111111111111,
               288.0
            ]
         },
         {  
            "breakdown_1":{  
               "id":80334,
               "name":"New York"
            },
            "synthetic_metrics":[  
               6261085.045296167,
               53.153741610400814,
               178.04181184668988,
               542.68055555555554,
               288.0
            ]
         },
         {  
            "breakdown_1":{  
               "id":79296,
               "name":"Boston"
            },
            "synthetic_metrics":[  
               1615718.1805555555,
               52.200071149402191,
               36.847222222222221,
               93.652777777777771,
               72.0
            ]
         }
      ]
   }
}

I tried your example but it’s not quite right I’m ending up with all the ID’s on one row for example.

$stats | Select-Object Start, End, @{Label = 'id'; Expression = {$_.summary.items.breakdown_1.id}}
4

Try this on for size

$json = @"
{  
   "start":"2015-10-19T10:08:05.7639282",
   "end":"2015-10-22T10:08:05.7639282",
   "timezone":{  
      "id":0,
      "name":"ET"
   },
   "summary":{  
      "fields":{  },
      "items":[  
         {  
            "breakdown_1":{  
               "id":61093,
               "name":"Toronto"
            },
            "synthetic_metrics":[  
               3845864.204861111,
               400.14501737223731,
               98.569444444444443,
               236.11111111111111,
               288.0
            ]
         },
         {  
            "breakdown_1":{  
               "id":80334,
               "name":"New York"
            },
            "synthetic_metrics":[  
               6261085.045296167,
               53.153741610400814,
               178.04181184668988,
               542.68055555555554,
               288.0
            ]
         },
         {  
            "breakdown_1":{  
               "id":79296,
               "name":"Boston"
            },
            "synthetic_metrics":[  
               1615718.1805555555,
               52.200071149402191,
               36.847222222222221,
               93.652777777777771,
               72.0
            ]
         }
      ]
   }
}
"@
$PSJson = $json | ConvertFrom-Json

ForEach ($item in $PSJson.Summary.Items) {
    [PSCustomObject]@{
        Start = $PSJson.start
        End = $PSJson.end
        id = $item.breakdown_1.id
        name = $item.breakdown_1.name
        metrics = $item.synthetic_metrics -join ", "
    }
}

Results:
Start : 2015-10-19T10:08:05.7639282
End : 2015-10-22T10:08:05.7639282
id : 61093
name : Toronto
metrics : 3845864.204861111, 400.14501737223731, 98.569444444444443, 236.11111111111111, 288.0

Start : 2015-10-19T10:08:05.7639282
End : 2015-10-22T10:08:05.7639282
id : 80334
name : New York
metrics : 6261085.045296167, 53.153741610400814, 178.04181184668988, 542.68055555555554, 288.0

Start : 2015-10-19T10:08:05.7639282
End : 2015-10-22T10:08:05.7639282
id : 79296
name : Boston
metrics : 1615718.1805555555, 52.200071149402191, 36.847222222222221, 93.652777777777771, 72.0

5

Or if you know your number of metrics are going to be consistent and you want each one as its own property

ForEach ($item in $PSJson.Summary.Items) {
    [PSCustomObject]@{
        Start = $PSJson.start
        End = $PSJson.end
        id = $item.breakdown_1.id
        name = $item.breakdown_1.name
        metric1 = $item.synthetic_metrics[0]
        metric2 = $item.synthetic_metrics[1]
        metric3 = $item.synthetic_metrics[2]
        metric4 = $item.synthetic_metrics[3]
        metric5 = $item.synthetic_metrics[4]
    }
}

Results in:
Start : 2015-10-19T10:08:05.7639282
End : 2015-10-22T10:08:05.7639282
id : 61093
name : Toronto
metric1 : 3845864.204861111
metric2 : 400.14501737223731
metric3 : 98.569444444444443
metric4 : 236.11111111111111
metric5 : 288.0

Start : 2015-10-19T10:08:05.7639282
End : 2015-10-22T10:08:05.7639282
id : 80334
name : New York
metric1 : 6261085.045296167
metric2 : 53.153741610400814
metric3 : 178.04181184668988
metric4 : 542.68055555555554
metric5 : 288.0

Start : 2015-10-19T10:08:05.7639282
End : 2015-10-22T10:08:05.7639282
id : 79296
name : Boston
metric1 : 1615718.1805555555
metric2 : 52.200071149402191
metric3 : 36.847222222222221
metric4 : 93.652777777777771
metric5 : 72.0

6

Or if you need to dynamically build out your Metrics based on how many are returned.
Note: This can have challenges with the Export-CSV cmdlet. If the target CSV exists and you are appending, you may not get Metrics that do not already exist as columns in the CSV.

ForEach ($item in $PSJson.Summary.Items) {
    $newitem = [PSCustomObject]@{
        Start = $PSJson.start
        End = $PSJson.end
        id = $item.breakdown_1.id
        name = $item.breakdown_1.name
    }
    For ($i = 0; $i -lt $item.synthetic_metrics.count; $i++) {
        Add-Member -InputObject $newitem -NotePropertyName "Metric$($i +1)" -NotePropertyValue $item.synthetic_metrics[$i]
    }
    $newitem
}

Results in:
Start : 2015-10-19T10:08:05.7639282
End : 2015-10-22T10:08:05.7639282
id : 61093
name : Toronto
Metric1 : 3845864.204861111
Metric2 : 400.14501737223731
Metric3 : 98.569444444444443
Metric4 : 236.11111111111111
Metric5 : 288.0

Start : 2015-10-19T10:08:05.7639282
End : 2015-10-22T10:08:05.7639282
id : 80334
name : New York
Metric1 : 6261085.045296167
Metric2 : 53.153741610400814
Metric3 : 178.04181184668988
Metric4 : 542.68055555555554
Metric5 : 288.0

Start : 2015-10-19T10:08:05.7639282
End : 2015-10-22T10:08:05.7639282
id : 79296
name : Boston
Metric1 : 1615718.1805555555
Metric2 : 52.200071149402191
Metric3 : 36.847222222222221
Metric4 : 93.652777777777771
Metric5 : 72.0

7

You sir are fantastic! Thank You, this is exactly what I needed. I spent hours trying to figure this out with only partial success.