Http table value pulling

wjaceh · February 20, 2019, 9:35pm

Hi, I would like to pull data from the http table.

https://www.nordpoolgroup.com/Market-data1/Dayahead/Area-Prices/EE/Hourly/?dd=EE&view=table

Http methods are a bit foreign to me and binding syntax is unclear.
Can someone give me one example how the highlighted value is pulled?
Something like this?
Number Electricity_price { http=">:[*:GET:…" }

David_Graeff · February 20, 2019, 9:59pm

This is a modern web application, not a static website. Look at “show source code” and you will not find that table in actual html.

The data is probably fetched from a json based http url. You first need to find out where the data is coming from.

Cheers, David

namraccr · February 21, 2019, 5:34am

Yep, and that would be from here:
https://www.nordpoolgroup.com/api/marketdata/page/47?currency=,,,EUR&entityName=EE

However, you should notice the big warning on the page as you consider doing this:

wjaceh · February 21, 2019, 7:53am

thanks for the link.

{"Index":1,"Scale":0,"SecondaryValue":null,"IsDominatingDirection":false,"IsValid":false,"IsAdditionalData":false,"Behavior":0,"Name":"20-02-2019","Value":"32,95","GroupHeader":null,"DisplayNegativeValueInBlue":false,"CombinedName":"20-02-2019","DateTimeForData":"0001-01-01T00:00:00","DisplayName":"32,95_True","DisplayNameOrDominatingDirection":"32,95","IsOfficial":true,"UseDashDisplayStyle":false}

data is here but I need to figure out the syntax for GET.
PS: it’s for personal use

David_Graeff · February 21, 2019, 12:00pm

Use any http example and replace the URL with the mentioned one. Then apply a json transformation to get your value.

Ideally you also find out if the URL allows further filtering (at the moment this is applied currency=,,,EUR&entityName=EE) to reduce the fetched data and relief the backend server.

wjaceh · February 21, 2019, 6:37pm

data is downloaded from URL according to log.
JSONPATH is more complicated than my previous experience.
I have used it for MQTT/Tasmota
from console:

19:09:30 MQT: tele/sonoff2/SENSOR = {"Time":"2019-02-21T19:09:30","SI7021":{"Temperature":1.3,"Humidity":70.1},"TempUnit":"C"}

Number      Sonoff4                 "Sonoff4 [%.1f %%]" { mqtt="<[broker:tele/sonoff2/SENSOR:state:JSONPATH($.SI7021.Humidity)]" }

Here the JSONPATH parameter is quite straightforward, but for http binding data I don’t understand how to describe the location as parameter names are not unique and they repeat, I should include somehow data row/column information as well…

{"data":
{"Rows":
[{"Columns":
[{"Index":0,
"Scale":0,
"SecondaryValue":null,
"IsDominatingDirection":false,
"IsValid":false,
"IsAdditionalData":false,
"Behavior":0,
"Name":"21-02-2019",
"Value":"41,54",
"GroupHeader":null,
"DisplayNegativeValueInBlue":false,
"CombinedName":"21-02-2019",
"DateTimeForData":"0001-01-01T00:00:00",
"DisplayName":"41,54_True",
"DisplayNameOrDominatingDirection":"41,54",
"IsOfficial":true,
"UseDashDisplayStyle":false},

{"Index":1,
"Scale":0,
"SecondaryValue":null,
"IsDominatingDirection":false,
"IsValid":false,
"IsAdditionalData":false,
"Behavior":0,
"Name":"20-02-2019",
"Value":"35,20",
"GroupHeader":null,
"DisplayNegativeValueInBlue":false,
"CombinedName":"20-02-2019",
"DateTimeForData":"0001-01-01T00:00:00",
"DisplayName":"35,20_True",
"DisplayNameOrDominatingDirection":"35,20",
"IsOfficial":true,
"UseDashDisplayStyle":false},

Number Electricity_price { http="<[https://www.nordpoolgroup.com/api/marketdata/page/47?currency=,,,EUR&amp;entityName=EE:60000:JSONPATH(????????)]" }

How is this sort of JSONPATH parameter constructed?

wjaceh · February 21, 2019, 7:07pm

something like this?:
JSONPATH($.data.Index[0].value)

wjaceh · February 22, 2019, 7:05pm

I’m still struggling with the JSONPATH parameter.
I have come up with the following: JSONPATH($data.Rows.Columns[0].Value[0])

but I’m getting the following error:

Transformation 'JSONPATH($data.Rows.Columns[0].Value[0])' threw an exception.

Rows is without index as there seems to be only one dimension of it.
Any JSONPATH advice?

namraccr · February 23, 2019, 12:21am

$data.Rows[0].Columns[0].Value

wjaceh · February 23, 2019, 9:14am

I tried that as well. Still gives error:

Transformation 'JSONPATH($data.Rows[0].Columns[0].Value)' threw an exception.

wjaceh · February 23, 2019, 9:55am

dot was missing in front of “data”.
now the full item is :

Number Electricity_price "Electricity_price [%.0f]" {http="<[https://www.nordpoolgroup.com/api/marketdata/page/47?currency=EUR&endDate={}:60000:JSONPATH($.data.Rows[0].Columns[0].Value)]" }

On the other hand value still doesn’t come through to sitemap.

I tested the new parameter in jsonpath.com

It can extract the value, but it is in quotes. So it’s a string?
How can I loose the quotes and convert in to number?

wjaceh · February 23, 2019, 11:31am

I tried to use this post as an example to create string to number conversion rule:
https://community.openhab.org/t/json-transformation-with-tuples-wont-work-and-how-to-convert-from-string-to-number-solved/34777/15

item:

Number Electricity_price "Electricity_price [%.2f]" {http="<[https://www.nordpoolgroup.com/api/marketdata/page/47?currency=EUR&endDate={}:60000:JSONPATH($.data.Rows[0].Columns[0].Value)]" }

sitemap:

	Text item=Electricity_price	label="Electricity_price [%.2f]"

rules:

var Number conversion
var String get_Electricity_price 
 rule "conversion"
when
  Item Electricity_price changed
then
  get_Electricity_price_state = Electricity_price.state.toString();
 conversion = Integer.valueOf(get_Electricity_price_state);
  Electricity_price.postUpdate(conversion)
end

but I’m still missing something as Sitemap value is not updated nor converted:

As a string it comes through to sitemap.

If I would define the item in the first place as a string then rule would have something to convert ( but data would still have quotes which I need to remove?!)
But if the converted value would be returned as a number into String item then it would result an error, right? Do I have to create a dummy object where converted value is returned?

David_Graeff · February 23, 2019, 1:03pm

Instead of “JSONPATH” you can also use the javascript transformation. Might be too much if you are new to this material though.

For now: Use a string item. Create a second item that is a Number. Use your rule to “postUpdate” to the Number item.

wjaceh · February 24, 2019, 9:22am

Thanks David.
OK, then I need to create the dummy item, but I still need to get rid of the quote marks somehow so the string would be suitable for the conversion in my rule.

David_Graeff · February 24, 2019, 10:37am

No you don’t. They mark the value as string. Your Integer.valueOf will already parse the number value out of it.

wjaceh · February 24, 2019, 12:52pm

I have now two items defined:

String Electricity_price "Electricity_price [%s]" {http="<[https://www.nordpoolgroup.com/api/marketdata/page/47?currency=EUR&endDate={}:60000:JSONPATH($.data.Rows[0].Columns[0].Value)]" }
Number Electricity_price2 "Electricity_price2 [%.2f]"

and rule is made to generate value for “Electricity_price2” dummy item:

var Number conversion
var String get_Electricity_price 
 rule "conversion"
when
  Item Electricity_price changed
then
        get_Electricity_price_state = Electricity_price.state.toString();
        conversion = Integer.valueOf(get_Electricity_price_state);
        Electricity_price2.postUpdate(conversion)
end

but values are not assigned to dummy item.
I did at one point add extra variable to rule (var Number Electricity_price2), but still no change.

I get that those questions/mistakes of mine are quite basic, but as I’m quite unexperienced with programming, it’s part of my learning process. Cheers.

wjaceh · February 24, 2019, 1:18pm

could the rule be right and problem is than as “Electricity_price” is not updated often? So there is nothing to call out the rule.
If so then how is initial startup conversion made for dummy item?

wjaceh · February 24, 2019, 7:27pm

How is item undefined value defined in rules?

var Number conversion
var String get_Electricity_price 
var Number Electricity_price2
 rule "conversion"
when 
    Item Electricity_price changed or
    Item Electricity_price2 == NULL or//gives error
    Item Electricity_price2 == "-"  //gives error
  
then
        get_Electricity_price_state = Electricity_price.state.toString();
        conversion = Integer.valueOf(get_Electricity_price_state);
        Electricity_price2.postUpdate(conversion)
end

with “==” I’m getting the following error.

Configuration model 'conversion.rules' has errors, therefore ignoring it: [7,5]: no viable alternative at input '=='

[8,5]: no viable alternative at input '=='

wjaceh · February 25, 2019, 6:43pm

I had error in variable name.

Now the rule is like this.
But I would like to add second condition which would assign value to Electricity_price2 if there is none.

var Number conversion
var String get_Electricity_price 
 rule "conversion"
when 
    Item Electricity_price changed //or
    //Item Electricity_price2 == NULL //gives error
  
then
        get_Electricity_price = Electricity_price.state.toString();
        conversion = Integer.valueOf(get_Electricity_price);
        Electricity_price2.postUpdate(conversion)
end

When I remove comments from second conditions I get an error.
How can I run the rule if value hasn’t been assigned to Electricity_price2 yet?

wjaceh · February 27, 2019, 8:34pm

As original problem was regarding pulling data from http binding and this was solved, I moved the rule problem to separate post.
https://community.openhab.org/t/rule-to-convert-string-to-number/68978
Thank you all for the help provided. Special thanks to David.