I’m looking for help with parsing of a json string from an API I’ve recently found. My coding skills are not great and therefore need a little assistance.
You could convert to String and select only the things you are interested in. I also looked at your jsonpath. It´s correct, but need some minor changes to work in my example. I´ve added some escape characters in the json output to get the example to work…
val String jsonString = new String("{\"Meta Data\":{\"1. Information\":\"Intraday (15min) prices and volumes\",\"2. Symbol\":\"LON:TW\",\"3. Last Refreshed\":\"2017-12-29 07:30:00\",\"4. Interval\":\"15min\",\"5. Output Size\":\"Compact\",\"6. Time Zone\":\"US/Eastern\"},\"Time Series (15min)\":{\"2017-12-29 07:30:00\":{\"1. open\":\"206.8000\",\"2. high\":\"208.3000\",\"3. low\":\"206.8000\",\"4. close\":\"208.1000\",\"5. volume\":\"393996\"},\"2017-12-29 07:15:00\":{\"1. open\":\"206.5310\",\"2. high\":\"207.0000\",\"3. low\":\"206.5000\",\"4. close\":\"206.8000\",\"5. volume\":\"316518\"}}}")
val String result = transform("JSONPATH","$.['Time Series (15min)'].[*].['1. open']",jsonString)
val String first = result.subSequence(3,11)
logInfo("First value :", first)
It’s a poorly designed piece of JSON. Had the time series been an array it would have been trivial. I don’t think you can do it in one line of JSONPATH as it is. I would probably use JavaScript to retrieve it.
I’ve made a small amount of progress with this and need further advice.
Here’s the rule I’ve created that works well, however as you can see it’s scheduled and that’s not beneficial as share prices move all the time. I’m looking for a way to check the URL and then to fire off the rule below.
rule "Get Current SL Share Price"
when
Time cron "0 2 8 * * ?"
then {
val String jsonString = sendHttpGetRequest('https://www.alphavantage.co/query?function=TIME_SERIES_INTRADAY&symbol=LON:SL&interval=15min&outputsize=compact&apik
ey=MYAPIKEYHERE')
if(jsonString != null) {
val String result = transform("JSONPATH","$.['Time Series (15min)'].[*].['1. open']",jsonString)
val String first = result.subSequence(2,10)
postUpdate(SL_Share_Price, first)
} else {
postUpdate(SL_Share_Price, "0.000")
}
}
end
I’ve tried using the http binding to update a String as below, but it’s producing an error.
when item stockPriceRaw changed
then
// do the sub sequence stuff here and update the SL_Share_Price item
end
In addition, you could change the way you get the first element of the json result. You could do the following to get the first element instead of using sub sequence.
The item produces the full list of share data, however I get this error every time and the share price item isn’t updated.
Rule 'TW Share Price': An error occurred during the script execution: The name '<XFeatureCallImplCustom>.subSequence(<XNumberLiteralImpl>,<XNumberLiteralImpl>)' cannot be resolved to an item or type.
Any ideas where I need to look to resolve this?
String TW_Status { http="<[https://www.alphavantage.co/query?function=TIME_SERIES_INTRADAY&symbol=LON:TW&interval=15min&outputsize=compact&apikey=MYAPIKEYHERE:60000:JSONPATH($.['Time Series (15min)'].[*].['1. open'])]" }
rule "TW Share Price"
when
Item TW_Status received update
then
val String current = TW_Status.subSequence(2,10)
postUpdate(TW_Share_Price, current)
end
I think the error is a result of you trying to apply subSequence to an Item while subSequence is a method of the String class.
Try this for your rule
rule "TW Share Price"
when
Item TW_Status received update
then
val String current = TW_Status.state.toString()
TW_Share_Price.postUpdate(current.subSequence(2,10))
end
String TW_Status { http="<[https://www.alphavantage.co/query?function=TIME_SERIES_INTRADAY&symbol=LON:TW&interval=15min&outputsize=compact&apikey=MYAPIKEY:900000:JSONPATH($.['Time Series (15min)'].[*].['1. open'])]" }
String TC_Status { http="<[https://www.alphavantage.co/query?function=TIME_SERIES_INTRADAY&symbol=LON:TCG&interval=15min&outputsize=compact&apikey=MYAPIKEY:900000:JSONPATH($.['Time Series (15min)'].[*].['1. open'])]" }
String SL_Status { http="<[https://www.alphavantage.co/query?function=TIME_SERIES_INTRADAY&symbol=LON:SLA&interval=15min&outputsize=compact&apikey=MYAPIKEY:900000:JSONPATH($.['Time Series (15min)'].[*].['1. open'])]" }
Number TW_Current_Price <line>
Number TC_Current_Price <line>
Number SL_Current_Price <line>
Number TW_Previous_Price <line>
Number TC_Previous_Price <line>
Number SL_Previous_Price <line>
Rules:
rule "Taylor Wimpey Share Price"
when
Item TW_Status received update
then
val String previous = TW_Current_Price.state.toString()
val String current = TW_Status.state.toString()
TW_Previous_Price.postUpdate(previous)
TW_Current_Price.postUpdate(current.subSequence(2,10))
end
rule "Thomas Cook Share Price"
when
Item TC_Status received update
then
val String previous = TC_Current_Price.state.toString()
val String current = TC_Status.state.toString()
TC_Previous_Price.postUpdate(previous)
TC_Current_Price.postUpdate(current.subSequence(2,10))
end
rule "Standard Life Share Price"
when
Item SL_Status received update
then
val String previous = SL_Current_Price.state.toString()
val String current = SL_Status.state.toString()
SL_Previous_Price.postUpdate(previous)
SL_Current_Price.postUpdate(current.subSequence(2,10))
end
Sitemap:
sitemap Shares label="Shares"
{
Frame label="Taylor Wimpey" {
Text item=TW_Current_Price label="Latest Price [%.2f]" valuecolor=[TW_Current_Price>TW_Previous_Price="green", <TW_Previous_Price="red"]
Text item=TW_Previous_Price label="Previous Price [%.2f]"
}
Frame label="Thomas Cook" {
Text item=TC_Current_Price label="Latest Price [%.2f]"
Text item=TC_Previous_Price label="Previous Price [%.2f]"
}
Frame label="Standard Life" {
Text item=SL_Current_Price label="Latest Price [%.2f]"
Text item=SL_Previous_Price label="Previous Price [%.2f]"
}
}
Now that I’ve upgrade to OH 2.3.0 the solution above doesn’t work, I believe that something has changed in the way that the transformation now works.
After a little searching of the forum I’ve found a possible alternative but need a little help getting the syntax sorted out.
val csv = sendHttpGetRequest("url to CSV DATA").split("\n")
val lastLine = csv.get(csv.size-1).split(",")
val T = lastLine.get(0).trim
val TimeStamp = lastLine.get(1).trim
val Price = lastLine.get(2).trim
The above works, but I’m looking for the first line not the last line. Here’s the content of the CSV data.
That’s my point, I have the syntax to obtain the lastline but not the first or even the second line. I found the code while searching, now I need a little advise on changing to meet my needs.
The term in parentheses determines which line is pulled from the csv:
csv.size is the total number of lines in the input. We subtract 1 to get the last line because lines are numbered starting from 0. If you want the first line, just use 0:
