Parsing json from ComEd Hourly Pricing

#1

1) Give a description of the problem
Working on a piston to consume ComEd’s current hour average price, available in json format at https://hourlypricing.comed.com/api?type=currenthouraverage

2) What is the expected behaviour?
I am able to log the response, but not access the json path, specifically the price element. Here’s an example of the format:

[{"millisUTC":"1565039700000","price":"3.1"}]

I thought $response[0].price would work, but it doesn’t.

3) What is happening/not happening?
Error retrieving json data part null

4) Post a Green Snapshot of the pistonimage

comedpiston
comedpiston.png1178×454 80.5 KB

5) Attach logs after turning logging level to Full
8/5/2019, 5:57:04 PM +795ms
+1ms ╔Received event [Home].test = 1565045824795 with a delay of 0ms
+91ms ║RunTime Analysis CS > 19ms > PS > 59ms > PE > 13ms > CE
+94ms ║Runtime (38022 bytes) successfully initialized in 59ms (v0.3.10a.20190223) (92ms)
+95ms ║╔Execution stage started
+102ms ║║Cancelling statement #1’s schedules…
+115ms ║║Sending external web request to: hourlypricing.comed.com/api?type=currenthouraverage&format=json
+342ms ║║Executed virtual command httpRequest (229ms)
+348ms ║║[{“millisUTC”:“1565045400000”,“price”:“3.5”}]
+349ms ║║Executed virtual command log (1ms)
+354ms ║║Error retrieving JSON data part null
+358ms ║║Executed virtual command log (1ms)
+360ms ║╚Execution stage complete. (265ms)
+362ms ╚Event processed successfully (361ms)

IFTTT Post Ingredients - Date/Time Problem
#2

It looks like that is an invalid json, as seen here:

temp

IFTTT Post Ingredients - Date/Time Problem
#3

If the price is always 9.9 or less, you can cheat a bit with a piston like this:

temp
temp.jpg771×313 32.6 KB

Pro Tip:
I suspect there is a hidden tab in the last 6 characters of $response…
Notice I had to use “right(string,7)” instead of “right(string,6)”

You can create more code to strip out any non ASCII chars, but the piston above looks much easier IMO.

#4

Confirmed. It is at the very end:

temp
temp.jpg838×135 17.6 KB

An unescaped tab is not valid in JSON strings.
It might be worth contacting ComEd, since it is an easy fix on their end.

#5

Interesting. I’m able to parse their json output with php - is there some way I could clean up the output in webcore prior to parsing it? I’m trying to deal with their text output as well, but it seems webcore doesn’t have any text functions that will allow me to reliably grab the price (it can be over 9.9 cents). Thanks.

#6

Actually, it looks like I can get it with:
decimal(mid($response,14))

(at least for the next few hundred years until the unix time adds a digit)

#7

The $response has to be stored in a variable to do this… and then you loose the ability to call the individual dataPoints. It is much easier & cleaner if the json comes in properly.

I am glad this worked for you. When I tested, it returned this string:
temp
(basically, it erased the first 14 characters: [{"millisUTC":)

When I changed the number to 39, I get the string:
2.6"}
so the tail end still needs to be cleaned up.

If that is getting pushed into a decimal variable, the 39 works as is, because a decimal variable cannot contain symbols:

temp

#8

I changed the output format to text from json on the query string (&format=text) - that explains the difference in characters count. Your way works too, though.

Thanks for the help!