Combining parts of a PSCustomObject

This topic contains 6 replies, has 2 voices, and was last updated by Profile photo of Yul Yul 1 year, 5 months ago.

  • Author
    Posts
  • #31174
    Profile photo of Yul
    Yul
    Participant

    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,

  • #31183
    Profile photo of Curtis Smith
    Curtis Smith
    Participant

    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

  • #31189
    Profile photo of Yul
    Yul
    Participant

    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}}
    
  • #31209
    Profile photo of Curtis Smith
    Curtis Smith
    Participant

    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

  • #31211
    Profile photo of Curtis Smith
    Curtis Smith
    Participant

    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

  • #31212
    Profile photo of Curtis Smith
    Curtis Smith
    Participant

    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

  • #31225
    Profile photo of Yul
    Yul
    Participant

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

You must be logged in to reply to this topic.