Accessing JSON arrays in Powershell

Question

I have the below JSON variable in Powershell and want to loop through each array to return the total value only. For example, I would expect $response2.results.daily[0], ...daily[1] ... etc would allow me to access each array in the array but it seems i have to declare the specific array name ex '2020-05-10'?

Is there a better way to handle APIs through PS?

There definitely is a way to *improve* the response from that API, so it's easier to handle. Don't use an object (`{daily: {"5/10/2020": {"total": "$0.030"}}}`), Use an array (`{daily: [{date: "2020-05-10", "total": "$0.030"}, ...]}`). Changing the data layout will make your life easier on more than one front, if you have any control over the API. — Tomalak, May 10 '20 at 21:43
This comes up a lot: https://stackoverflow.com/questions/33520699/iterating-through-a-json-file-powershell — js2010, May 11 '20 at 02:05

score 2 · Answer 1 · answered May 10 '20 at 21:30

You can use the hidden psobject property to obtain the individual property names on the $response2.results.daily object, then use those to access each array:

# use psobject to enumerate the property names (ie. '5/10/2020')
$dates = $response2.results.daily.psobject.Properties.Name

# loop through the property names
$dailyStats = $dates |ForEach-Object {
  $date = $_
  # Add the name (= date) as a new "date" property to the object
  $response2.results.daily.$name |Select-Object @{Name='date';Expression={$name}},*
}

$dailyStats will now be an array of objects like:

date      total   sms-in sms-out suppress
----      -----   ------ ------- --------
5/10/2020 $31.770 102    2,016   1
5/9/2020  $0.060  4      0       0
etc...

Is there a better way to handle APIs through PS?

No idea, given that we don't know how you obtained the response in the first place ¯\_(ツ)_/¯

Accessing JSON arrays in Powershell

1 Answers1