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.

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.