Combining parts of a PSCustomObject

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

  • Author
    Posts
  • #31174

    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

    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

    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

    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

    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

    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

    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.