Nested JSON using PowerShell

Question

My JSON looks like this:

{
  "data": [
    {
      "name": "engagement",
      "period": "lifetime",
      "values": [
        {
          "value": 52
        }
      ],
      "title": "Engagement",
      "description": "Total number of likes and comments on the media object",
      "id": "1798601712/insights/engagement/lifetime"
    },
    {
      "name": "impressions",
      "period": "lifetime",
      "values": [
        {
          "value": 796
        }
      ],
      "title": "Impressions",
      "description": "Total number of times the media object has been seen",
      "id": "1798601712/insights/impressions/lifetime"
    }
  ]
}

What I managed to achieve at this moment:

"1798601712/insights/engagement/lifetime","engagement","52"
"1798601712/insights/impressions/lifetime","impressions","796"
"1798601712/insights/reach/lifetime","reach","422"

Using the following code:

$Ident = Import-Csv -Path ".\src\Process.txt" -Header $Header |
         Select-Object -Skip 2
foreach ($idka in $ident) {
    $sid = $idka.id
    $request_n = "https://api/"+ $sid +"/data=20190101&file=json"
    foreach($dane1 in $request_n) {
        Invoke-WebRequest $dane1 |
            ConvertFrom-Json |
            Select -ExpandProperty data |
            Select id, name, @{label = "values";Expression ={$_.values.value}} |
            Export-Csv $filename -NoTypeInformation -Append
    }
}

I need my csv to look like this:

id  engagement  impressions reach
1798601712  52  796 422
1786717942  34  428 346
1787997335  29  376 281
1788199840  30  532 439
1788311007  48  1053 867
1788353947  28  609 497
1788403484  43  809 460

Please make sure that your sample input corresponds to the sample output you're showing and vice versa. — Ansgar Wiechers
– Ansgar Wiechers, Commented Apr 3, 2019 at 10:21

Ansgar Wiechers · Accepted Answer · 2019-04-03 10:20:13Z

1

After expanding the data array group the nested objects by the ID you extract from the id field. For each group build a hashtable in which you map the values from each nested object to their name property. Create a custom object from the hashtable, then export the result to the output CSV.

...|
Select-Object -Expand data |
Group-Object { $_.id.Split('/')[0] } |
ForEach-Object {
    $prop = @{
        'id' = $_.Name
    }
    $_.Group | ForEach-Object {
        $prop[$_.name] = $_.values.value
    }
    New-Object -Type PSObject -Property $prop
} |
Select-Object id, engagement, impressions, reach |
Export-Csv $filename -NoType -Append

Note that with PowerShell v3 or newer you can use an ordered hashtable and the [PSCustomObject] type accelerator instead of New-Object, which would allow you to omit the last Select-Object (whose sole purpose is getting the output fields in the desired order).

answered Apr 3, 2019 at 10:20

Ansgar Wiechers

202k27 gold badges286 silver badges363 bronze badges

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

4 Comments

mario Over a year ago

And is it still possible to add my own column with today's date ?

Ranadip Dutta Over a year ago

@mario : Yes it is; using get-date function

mario Over a year ago

I know this command only I wanted to add it to each line and I do not want to work

Ansgar Wiechers Over a year ago

@mario Please do not move the target. Adding a date column was not part of your question.

Collectives™ on Stack Overflow

Nested JSON using PowerShell

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related