Hi,

I am trying to use powershell to connect to a web service api, download JSON data and convert to CSV. 

An example set of raw JSON data is 

{"results":[{"count":297},{"result":73.4006734006734},{"result":26.599326599326602},{"result":0.0}],"performanceStats":{"fileReadCount":1,"inspectedCount":297,"matchCount":297,"processCount":1,"fileProcessingTime":2,"mergeTime":0,"ioTime":2,"wallClockTime":7,"fullCacheHits":0,"partialCacheHits":0,"cacheMisses":0,"cacheSkipped":1,"maxInspectedCount":297,"minInspectedCount":297},"metadata":{"eventTypes":["PageView"],"eventType":"PageView","openEnded":true,"beginTime":"2015-02-08T17:02:44Z","endTime":"2015-02-09T17:02:44Z","beginTimeMillis":1423414964679,"endTimeMillis":1423501364679,"rawSince":"1 DAYS AGO","rawUntil":"`now`","rawCompareWith":"","guid":"70b2568a-eec3-d7dd-36b5-85a9b623dccc","routerGuid":"9d6beb20-1393-83d3-74f3-865cb4e7b823","contents":[{"function":"alias","alias":"Total Page Views","contents":{"function":"count","simple":true,"openEnded":false}},{"function":"alias","alias":"IE","contents":{"function":"percentage","simple":true,"of":{"function":"count","simple":true,"openEnded":false},"filter":"userAgentName = 'IE'"}},{"function":"alias","alias":"Chrome","contents":{"function":"percentage","simple":true,"of":{"function":"count","simple":true,"openEnded":false},"filter":"userAgentName = 'Chrome'"}},{"function":"alias","alias":"Firefox","contents":{"function":"percentage","simple":true,"of":{"function":"count","simple":true,"openEnded":false},"filter":"userAgentName = 'Firefox'"}}]}}

My ideal csv output would be the values in "result" properly aliased with the values in "metadata"

Total Page Views,IE,Chrome,Firefox

297,73.4006734006734,26.599326599326602,0

I have no need for the "Performance Stats"

If I use the "ConvertFrom-JSon" command I get three objects - results, performanceStats and metadata. The format of the converted results object is 

{@{count=295}, @{result=73.89830508474576}, @{result=26.101694915254235}, @{result=0.0}}

How do I get that into CSV in a useable format? I'm guessing it may be easier to do my headers manually for each query? 

Thanks! 

What cmdlet did you use to get the data from the API? Invoke-RestMethod abstracts all this for you so you don't even have to mess with the JSON - it just returns Powershell objects. This can easily be exported to csv:

Invoke-RestMethod -Uri https://whatev.com/api/whatever | Export-Csv -Path c:\whatever.csv 

I get the same result of 

{@{count=295}, @{result=73.89830508474576}, @{result=26.101694915254235}, @{result=0.0}}

if I do 

Invoke-WebRequest -Uri "URL" | ConvertFrom-Json | select results

or 

Invoke-RestMethod -Uri "URL" | select results

If that's correct, how do I break out the @{var=value} into var/value pairs for csv formatting - ConvertTo-CSV doesn't work either! 

I get the same result of 

{@{count=295}, @{result=73.89830508474576}, @{result=26.101694915254235}, @{result=0.0}}

if I do 

Invoke-WebRequest -Uri "URL" | ConvertFrom-Json | select results

or 

Invoke-RestMethod -Uri "URL" | select results

If that's correct, how do I break out the @{var=value} into var/value pairs for csv formatting - ConvertTo-CSV doesn't work either! 

just try the Invoke-RestMethod part without piping. It should parse the json and return objects.

What you're dealing with is deeply nested objects returned from you API. The command I gave you above essentially drills in two levels deep to the Result property of the Results property which gives you a total count and three percentage values. I used the JSON viewer plugin in Notepad++ to get an idea of what the object returned looks like:

However, the metadata property is even more deeply nested so you may need to play with this:

So in other words, you can use Invoke-RestMethod to grab the object, then dig into it to get all the properties you need, then output a custom object which can be exported to csv:

$Json = Invoke-RestMethod -Uri $Uri

$Results = $Json.results
$Contents = $Json.metadata.contents
$Output = [ordered]@{ }
for ($i=0; $i -lt $Contents.count; $i++)
{
    if ($Contents.alias[$i] -eq 'Total Page Views')
    {
        $Output."$($Contents.alias[$i])" = $Results[0].count 
    }
    else
    {
        $Output."$($Contents.alias[$i])" = $Results.Result[$i]
    }
}

New-Object -typename PSObject -Property $Output

The structure of the Json isn't very orderly so this takes a bit of monkeying!

• Marked as answer by superdaveuk Wednesday, February 11, 2015 9:37 AM

If I just do the Invoke-RestMethod, then I get three objects - results, performanceStats and metadata. But they are all in same format as before. 

The exact response I get is (formatting isn't great)

results                                 performanceStats                        metadata
-------                                 ----------------                        --------
{@{count=295}, @{result=73.898305084... @{fileReadCount=1; inspectedCount=29... @{eventTypes=System.Object[]; eventT...

As I don't need the performance stats and metadata I'd filtered them out. 

If I just do the Invoke-RestMethod, then I get three objects - results, performanceStats and metadata. But they are all in same format as before. 

The exact response I get is (formatting isn't great)

results                                 performanceStats                        metadata
-------                                 ----------------                        --------
{@{count=295}, @{result=73.898305084... @{fileReadCount=1; inspectedCount=29... @{eventTypes=System.Object[]; eventT...

As I don't need the performance stats and metadata I'd filtered them out. 

What happens if you do this:

Invoke-RestMethod -Uri "URL" | select -expandproperty results

Almost there! 

If I do select -expandproperty results then I just get

count
-----
  470

(The data has changed from the above example from 295 page views to 470).

I don't get any of the other data, which are the % of page views for each browser. 

However, if I do select -expandproperty performanceStats then I just get

fileReadCount      : 3
inspectedCount     : 562
matchCount         : 470
processCount       : 3
fileProcessingTime : 2
mergeTime          : 0
ioTime             : 2
wallClockTime      : 7
fullCacheHits      : 1
partialCacheHits   : 0
cacheMisses        : 0
cacheSkipped       : 2
maxInspectedCount  : 297
minInspectedCount  : 24

Which appears to be the complete set from the original JSON output. The same is true of metadata:

eventTypes      : {PageView}
eventType       : PageView
openEnded       : True
beginTime       : 2015-02-09T13:17:14Z
endTime         : 2015-02-10T13:17:14Z
beginTimeMillis : 1423487834145
endTimeMillis   : 1423574234145
rawSince        : 1 DAYS AGO
rawUntil        : `now`
rawCompareWith  :
guid            : 3c4d61b1-4413-2bd6-ff0f-cf11ed32b687
routerGuid      : c7cef9c4-c84e-b1a5-618e-5f729be666d3
contents        : {@{function=alias; alias=Total Page Views; contents=}, @{function=alias; alias=IE; contents=},
                  @{function=alias; alias=Chrome; contents=}, @{function=alias; alias=Firefox; contents=}}

It's frustrating that it only appears to be these values that are not displaying. 

Try 

(Invoke-RestMethod -Uri "URL").Results.result

So, that returns the 3 percentage values - 

78.61507128309573
21.384928716904277
0.0

If I do just

(Invoke-RestMethod -Uri "URL").Results

then I get 

Count

-------

492

If I do 

(Invoke-RestMethod -Uri "URL").Results | format-wide

Then I get

493                                                         75.65922920892496
24.34077079107505                                           0.0

but If I pipe that to export-csv, then I get trash in my CSV file. 

What you're dealing with is deeply nested objects returned from you API. The command I gave you above essentially drills in two levels deep to the Result property of the Results property which gives you a total count and three percentage values. I used the JSON viewer plugin in Notepad++ to get an idea of what the object returned looks like:

However, the metadata property is even more deeply nested so you may need to play with this:

So in other words, you can use Invoke-RestMethod to grab the object, then dig into it to get all the properties you need, then output a custom object which can be exported to csv:

$Json = Invoke-RestMethod -Uri $Uri

$Results = $Json.results
$Contents = $Json.metadata.contents
$Output = [ordered]@{ }
for ($i=0; $i -lt $Contents.count; $i++)
{
    if ($Contents.alias[$i] -eq 'Total Page Views')
    {
        $Output."$($Contents.alias[$i])" = $Results[0].count 
    }
    else
    {
        $Output."$($Contents.alias[$i])" = $Results.Result[$i]
    }
}

New-Object -typename PSObject -Property $Output

The structure of the Json isn't very orderly so this takes a bit of monkeying!

• Marked as answer by superdaveuk 1 hour 48 minutes ago

Brilliant - thanks for all your help!! 

In case anyone is wondering what this is for - it's for getting data out of New Relic's insight product into a monthly report.