Sort & select elements from JSON string within rules

Hi All,

I’ve been learning OpenHAB for a few months now and have been incredibly appreciative of the questions and answers posted on this community forum. I have a problem where I am now looking for some advice on the best way to approach it.

Essentially, my electricity retailer provides me with the wholesale price of electricity, which is updated every half an hour. I can access this data via an API, in which a very long JSON string is returned. I have successfully been able to set up a rule to query the API, receive the JSON string and extract elements of the JSON string using the JSONPATH transform.

However, to get the right pricing information, there are several further steps which I need to perform as follows:

  • sort the elements of a part of the JSON string based on a time stamp (formatted as string “2020-10-17T19:30:00” so should be able to directly sort it as a String type I presume?)
  • find the first entry where the periodType is “FORECAST” and periodSource is “30MIN”
  • then, within that dictionary entry, get the value of the key “wholesaleKWHPrice”

From the information I have found so far, it doesn’t seem that sorting elements of a JSON string, or finding elements based on other attributes can be directly done within a DSL rule, though I am happy to be corrected on this. Are my options then to code this in an external script, such as java or python, which the rule then calls upon? For reference, an extract of the returned JSON string is as follows:
{
“periodType”: “ACTUAL”,
“semiScheduledGeneration”: “940.2”,
“operationalDemand”: “4679.1”,
“rooftopSolar”: “0”,
“createdAt”: “2020-10-17T20:04:41”,
“wholesaleKWHPrice”: “4.2339”,
“region”: “VIC1”,
“period”: “2020-10-17T20:00:00”,
“renewablesPercentage”: “0.2009360774507918”,
“periodSource”: “30MIN”,
“percentileRank”: “0.375”
},
{
“period”: “2020-10-17T20:30:00”,
“periodType”: “ACTUAL”,
“periodSource”: “5MIN”,
“latestPeriod”: “2020-10-17T20:05:00”,
“region”: “VIC1”,
“rooftopSolar”: “0”,
“usage”: “27744.24000”,
“operationalDemand”: “27744.24000”,
“wholesaleKWHPrice”: “5.17809”,
“renewablesPercentage”: “0.21034”,
“percentileRank”: “0.7767857142857143”
},
{
“periodType”: “FORECAST”,
“semiScheduledGeneration”: “929.777”,
“operationalDemand”: “4430.98”,
“rooftopSolar”: “0”,
“forecastedAt”: “2020-10-17T20:00:00”,
“forecastedAt+period”: “2020-10-17T20:00:00+2020-10-17T21:00:00”,
“createdAt”: “2020-10-17T20:04:41”,
“wholesaleKWHPrice”: “4.3283955”,
“period”: “2020-10-17T21:00:00”,
“region”: “VIC1”,
“renewablesPercentage”: “0.20983552171302966”,
“periodSource”: “30MIN”,
“percentileRank”: “0.41964285714285715”
},

My OpenHAB is running on a raspberry pi 3 with openHABian.
I’d appreciate any guidance on the best way to solve this. Thanks in advance!
Felix

Well yes, a string is a string and does not have elements in DSL.
DSL rules can use keyed array-like objects called “maps” which can do some of the tricks required here like sorting. The tedious part would be extracting data to load them with.

The openHAB JSONPATH transformation can pull off some reasonably complicated extractions - search this forum for something like “jsonpath array” for examples. But in other ways it greatly constrains JSON handling in rules, mostly because you can only extract one result at a time, as a single string.

Need an example ‘what you want’ to go with the example JSON, really.

1 Like

Thanks for the reply and the info about jsonpath arrays. To clarify what I am after, the following JSON string is what I have extracted from the complete JSON string using the JSON Transform (I’ve only shown a 5 element array, but it is actually hundreds of elements):

…,
{
“periodType” : “ACTUAL”,
“semiScheduledGeneration” : “312.66”,
“operationalDemand” : “4728.94”,
“rooftopSolar” : “0”,
“createdAt” : “2020-10-18T21:05:41”,
“wholesaleKWHPrice” : “4.7509”,
“region” : “VIC1”,
“period” : “2020-10-18T21:00:00”,
“renewablesPercentage” : “0.0661162966753649”,
“periodSource” : “30MIN”,
“percentileRank” : “0.7454545454545455”
},
{
“period” : “2020-10-18T21:30:00”,
“periodType” : “ACTUAL”,
“periodSource” : “5MIN”,
“latestPeriod” : “2020-10-18T21:10:00”,
“region” : “VIC1”,
“rooftopSolar” : “0”,
“usage” : “27829.71000”,
“operationalDemand” : “27829.71000”,
“wholesaleKWHPrice” : “4.43152”,
“renewablesPercentage” : “0.06834”,
“percentileRank” : “0.38181818181818183”
},
{
“periodType” : “FORECAST”,
“semiScheduledGeneration” : “350.528”,
“operationalDemand” : “4422.45”,
“rooftopSolar” : “0”,
“forecastedAt” : “2020-10-18T21:00:00”,
“forecastedAt+period” : “2020-10-18T21:00:00+2020-10-18T22:00:00”,
“createdAt” : “2020-10-18T21:05:41”,
“wholesaleKWHPrice” : “4.5254957000000005”,
“period” : “2020-10-18T22:00:00”,
“region” : “VIC1”,
“renewablesPercentage” : “0.07926104308697668”,
“periodSource” : “30MIN”,
“percentileRank” : “0.5”
},
{
“periodType” : “FORECAST”,
“semiScheduledGeneration” : “348.101”,
“operationalDemand” : “4268.82”,
“rooftopSolar” : “0”,
“forecastedAt” : “2020-10-18T21:00:00”,
“forecastedAt+period” : “2020-10-18T21:00:00+2020-10-18T22:30:00”,
“createdAt” : “2020-10-18T21:05:41”,
“wholesaleKWHPrice” : “4.8887993000000005”,
“period” : “2020-10-18T22:30:00”,
“region” : “VIC1”,
“renewablesPercentage” : “0.08154501712417014”,
“periodSource” : “30MIN”,
“percentileRank” : “0.7818181818181819”
},
{
“periodType” : “FORECAST”,
“semiScheduledGeneration” : “343.872”,
“operationalDemand” : “4210.72”,
“rooftopSolar” : “0”,
“forecastedAt” : “2020-10-18T21:00:00”,
“forecastedAt+period” : “2020-10-18T21:00:00+2020-10-18T23:00:00”,
“createdAt” : “2020-10-18T21:05:41”,
“wholesaleKWHPrice” : “4.3353760999999995”,
“period” : “2020-10-18T23:00:00”,
“region” : “VIC1”,
“renewablesPercentage” : “0.08166584337120493”,
“periodSource” : “30MIN”,
“percentileRank” : “0.32727272727272727”
}, …etc

What I want is the value “4.5254957000000005” (in bold), being the value for the “wholesaleKWHPrice” when the periodType = “FORECAST” and with the earliest timestamp as the value of the key “period”. While this JSON string is in chronological order based on “period”, they aren’t always, hence why I want to sort the elements of the returned array in ascending order by “period” first. I hope that helps clarify what I want. Thanks!

There was recent interest in this, but in a forum hiccup the recent posts got lost.
It did remind me I’d started to play with this out of interest, but never finished. So now I did.

Using your JSON -

import java.util.HashMap
import java.util.ArrayList
import java.util.Collections


val rawjson = '[
{"periodType" : "ACTUAL","semiScheduledGeneration" : "312.66","operationalDemand" : "4728.94","rooftopSolar" : "0","createdAt" : "2020-10-18T21:05:41","wholesaleKWHPrice" : "4.7509","region" : "VIC1","period" : "2020-10-18T21:00:00","renewablesPercentage" : "0.0661162966753649","periodSource" : "30MIN","percentileRank" : "0.7454545454545455"
},
{"period" : "2020-10-18T21:30:00","periodType" : "ACTUAL","periodSource" : "5MIN","latestPeriod" : "2020-10-18T21:10:00","region" : "VIC1","rooftopSolar" : "0","usage" : "27829.71000","operationalDemand" : "27829.71000","wholesaleKWHPrice" : "4.43152","renewablesPercentage" : "0.06834","percentileRank" : "0.38181818181818183"
},
{"periodType" : "FORECAST","semiScheduledGeneration" : "350.528","operationalDemand" : "4422.45","rooftopSolar" : "0","forecastedAt" : "2020-10-18T21:00:00","forecastedAt+period" : "2020-10-18T21:00:00+2020-10-18T22:00:00","createdAt" : "2020-10-18T21:05:41","wholesaleKWHPrice" : "4.5254957000000005","period" : "2020-10-18T22:00:00","region" : "VIC1","renewablesPercentage" : "0.07926104308697668","periodSource" : "30MIN","percentileRank" : "0.5"
},
{"periodType" : "FORECAST","semiScheduledGeneration" : "348.101","operationalDemand" : "4268.82","rooftopSolar" : "0","forecastedAt" : "2020-10-18T21:00:00","forecastedAt+period" : "2020-10-18T21:00:00+2020-10-18T22:30:00","createdAt" : "2020-10-18T21:05:41","wholesaleKWHPrice" : "4.8887993000000005","period" : "2020-10-18T22:30:00","region" : "VIC1","renewablesPercentage" : "0.08154501712417014","periodSource" : "30MIN","percentileRank" : "0.7818181818181819"
},
{"periodType" : "FORECAST","semiScheduledGeneration" : "343.872","operationalDemand" : "4210.72","rooftopSolar" : "0","forecastedAt" : "2020-10-18T21:00:00","forecastedAt+period" : "2020-10-18T21:00:00+2020-10-18T23:00:00","createdAt" : "2020-10-18T21:05:41","wholesaleKWHPrice" : "4.3353760999999995","period" : "2020-10-18T23:00:00","region" : "VIC1","renewablesPercentage" : "0.08166584337120493","periodSource" : "30MIN","percentileRank" : "0.32727272727272727"
}
]' 

var forecastWS="none found" // result placeholders
var forecastStamp = "-"

var PDresults = transform("JSONPATH", "$.[?(@.periodType=='FORECAST')].period", rawjson)
   // get string results like ["2020-10-18T22:00:00", "2020-10-18T22:30:00", "2020-10-18T23:00:00"]
var WSresults = transform("JSONPATH", "$.[?(@.periodType=='FORECAST')].wholesaleKWHPrice", rawjson)
   // get string results like ["4.5254957000000005", "4.8887993000000005", "4.3353760999999995"]
   // note these are not arrays

if (PDresults !== null && WSresults !== null) { // check found some
    PDresults = PDresults.replace("[","").replace("]","")
    val PDlist = PDresults.split(",")
    WSresults = WSresults.replace("[","").replace("]","")
    val WSlist = WSresults.split(",")
        // now as Lists
    val masterMap = new HashMap<String,String>()
    for (var int i = 0; i < PDlist.size ; i++) {
        masterMap.put(PDlist.get(i),WSlist.get(i))  // key-value
    }
    // all that to load hashmap, must be a more elegant way!
    val sortedKeys=new ArrayList(masterMap.keySet())
    Collections::sort(sortedKeys)  //just sort keys as strings works okay
    forecastWS = masterMap.get(sortedKeys.last) // use bottom key for oldest
    forecastStamp = sortedKeys.last
}
logInfo("test", "Oldest forecast - " + forecastWS + " on " + forecastStamp )

outputs - Oldest forecast - “4.5254957000000005” on “2020-10-18T22:00:00”

Thanks so much for the ideas! I’m still learning about a few of the items you’ve used in your solution, but one thing I was wondering was why have you used a type ‘val’ instead of ‘var’ for PDlist and WSlist, given these will change at every update?

I’ve copied your code, but have made an error somewhere as the JSON transform for the PDresults and WSresults are returning NULL (I did enter the syntax you’ve used in an online evaluator, and it works with the input (rawjson) I have used, so I’ve done something else wrong).

import java.util.HashMap
import java.util.ArrayList
import java.util.Collections

val String filename = "power_price.rules"

rule "Get wholesale power price"
when
    Time cron "0 0/1 * 1/1 * ? * "    // or trigger every 30 minutes of every day starting on the 1st of each month (currently set to 1 minute for testing)
    
then
    // Temporary JSON response for testing
    var String response_json = '{"serviceResponseType":1,"data":{"currentNEMtime":"2021-02-11T20:33:42","postcode":"","networkProvider":"XXXXX","staticPrices":{"E1":{"dataAvailable":true,"networkDailyPrice":"18.04","basicMeterDailyPrice":"0","additionalSmartMeterDailyPrice":"15.3395","amberDailyPrice":"32.87671233","totalDailyPrice":"64.27779452","networkKWHPrice":"8.437","marketKWHPrice":"2.868","greenKWHPrice":"3.6388","carbonNeutralKWHPrice":"0.11","lossFactor":"1.05239169","offsetKWHPrice":"0.11","totalfixedKWHPrice":"11.41500","totalBlackPeakFixedKWHPrice":"NaN","totalBlackShoulderFixedKWHPrice":"NaN","totalBlackOffpeakFixedKWHPrice":"NaN"},"E2":{"dataAvailable":true,"networkDailyPrice":"0","basicMeterDailyPrice":"0","additionalSmartMeterDailyPrice":"0","amberDailyPrice":"0","totalDailyPrice":"0","networkKWHPrice":"1.903","marketKWHPrice":"2.868","greenKWHPrice":"3.6388","carbonNeutralKWHPrice":"0.11","lossFactor":"1.05239169","offsetKWHPrice":"0.11","totalfixedKWHPrice":"4.88100","totalBlackPeakFixedKWHPrice":"NaN","totalBlackShoulderFixedKWHPrice":"NaN","totalBlackOffpeakFixedKWHPrice":"NaN"},"B1":{"dataAvailable":true,"networkDailyPrice":"0","basicMeterDailyPrice":"0","additionalSmartMeterDailyPrice":"0","amberDailyPrice":"0","totalDailyPrice":"0","networkKWHPrice":"0","marketKWHPrice":"0","greenKWHPrice":"0","carbonNeutralKWHPrice":"0","lossFactor":"-1.05239169","offsetKWHPrice":"0","totalfixedKWHPrice":"0.00000","totalBlackPeakFixedKWHPrice":"NaN","totalBlackShoulderFixedKWHPrice":"NaN","totalBlackOffpeakFixedKWHPrice":"NaN"}},"variablePricesAndRenewables":[{"periodType":"ACTUAL","semiScheduledGeneration":"554.53","operationalDemand":"5972.27","rooftopSolar":"0","createdAt":"2021-02-11T20:32:41","wholesaleKWHPrice":"4.2064","region":"VIC1","period":"2021-02-10T21:00:00","renewablesPercentage":"0.0928507920773843","periodSource":"30MIN","percentileRank":"0.8468468468468469"},{"periodType":"ACTUAL","semiScheduledGeneration":"615.21","operationalDemand":"5621.66","rooftopSolar":"0","createdAt":"2021-02-11T20:32:41","wholesaleKWHPrice":"3.7301","region":"VIC1","period":"2021-02-10T21:30:00","renewablesPercentage":"0.10943564712202447","periodSource":"30MIN","percentileRank":"0.6486486486486487"},{"periodType":"ACTUAL","semiScheduledGeneration":"593.12","operationalDemand":"5333.73","rooftopSolar":"0","createdAt":"2021-02-11T20:32:41","wholesaleKWHPrice":"3.4441","region":"VIC1","period":"2021-02-10T22:00:00","renewablesPercentage":"0.11120172937137801","periodSource":"30MIN","percentileRank":"0.5045045045045045"},{"periodType":"ACTUAL","semiScheduledGeneration":"598.37","operationalDemand":"5072.68","rooftopSolar":"0","createdAt":"2021-02-11T20:32:41","wholesaleKWHPrice":"3.4309000000000003","region":"VIC1","period":"2021-02-10T22:30:00","renewablesPercentage":"0.11795934299029309","periodSource":"30MIN","percentileRank":"0.4954954954954955"},{"periodType":"ACTUAL","semiScheduledGeneration":"600.1","operationalDemand":"4917.48","rooftopSolar":"0","createdAt":"2021-02-11T20:32:41","wholesaleKWHPrice":"2.9887000000000006","region":"VIC1","period":"2021-02-10T23:00:00","renewablesPercentage":"0.12203404996054891","periodSource":"30MIN","percentileRank":"0.27927927927927926"},{"periodType":"ACTUAL","semiScheduledGeneration":"579.83","operationalDemand":"5056.43","rooftopSolar":"0","createdAt":"2021-02-11T20:32:41","wholesaleKWHPrice":"3.5408999999999997","region":"VIC1","period":"2021-02-10T23:30:00","renewablesPercentage":"0.1146718139082317","periodSource":"30MIN","percentileRank":"0.5495495495495496"},{"periodType":"ACTUAL","semiScheduledGeneration":"568.68","operationalDemand":"4972.53","rooftopSolar":"0","createdAt":"2021-02-11T20:32:41","wholesaleKWHPrice":"3.5541000000000005","region":"VIC1","period":"2021-02-11T00:00:00","renewablesPercentage":"0.11436431756067836","periodSource":"30MIN","percentileRank":"0.5585585585585585"},{"periodType":"ACTUAL","semiScheduledGeneration":"522.71","operationalDemand":"4758.07","rooftopSolar":"0","createdAt":"2021-02-11T20:32:41","wholesaleKWHPrice":"3.4639","region":"VIC1","period":"2021-02-11T00:30:00","renewablesPercentage":"0.10985756829975181","periodSource":"30MIN","percentileRank":"0.5225225225225225"},{"periodType":"ACTUAL","semiScheduledGeneration":"522.18","operationalDemand":"4570.57","rooftopSolar":"0","createdAt":"2021-02-11T20:32:41","wholesaleKWHPrice":"3.3407000000000004","region":"VIC1","period":"2021-02-11T01:00:00","renewablesPercentage":"0.1142483322649035","periodSource":"30MIN","percentileRank":"0.45045045045045046"},{"periodType":"ACTUAL","semiScheduledGeneration":"583.77","operationalDemand":"4407.81","rooftopSolar":"0","createdAt":"2021-02-11T20:32:41","wholesaleKWHPrice":"3.0382000000000007","region":"VIC1","period":"2021-02-11T01:30:00","renewablesPercentage":"0.13243991914352024","periodSource":"30MIN","percentileRank":"0.2972972972972973"},{"periodType":"ACTUAL","semiScheduledGeneration":"692.96","operationalDemand":"4252.45","rooftopSolar":"0","createdAt":"2021-02-11T20:32:41","wholesaleKWHPrice":"2.3617","region":"VIC1","period":"2021-02-11T02:00:00","renewablesPercentage":"0.16295547272748653","periodSource":"30MIN","percentileRank":"0.24324324324324326"},{"periodType":"ACTUAL","semiScheduledGeneration":"819.17","operationalDemand":"4108.61","rooftopSolar":"0","createdAt":"2021-02-11T20:32:41","wholesaleKWHPrice":"1.2122","region":"VIC1","period":"2021-02-11T02:30:00","renewablesPercentage":"0.19937886535835722","periodSource":"30MIN","percentileRank":"0.16216216216216217"},{"periodType":"ACTUAL","semiScheduledGeneration":"962.11","operationalDemand":"4068.99","rooftopSolar":"0","createdAt":"2021-02-11T20:32:41","wholesaleKWHPrice":"0.9284000000000001","region":"VIC1","period":"2021-02-11T03:00:00","renewablesPercentage":"0.23644934000820844","periodSource":"30MIN","percentileRank":"0.09009009009009009"},{"periodType":"ACTUAL","semiScheduledGeneration":"1087.8","operationalDemand":"4003.01","rooftopSolar":"0","createdAt":"2021-02-11T20:32:41","wholesaleKWHPrice":"0.8503000000000002","region":"VIC1","period":"2021-02-11T03:30:00","renewablesPercentage":"0.2717455115025943","periodSource":"30MIN","percentileRank":"0.06306306306306306"},{"periodType":"ACTUAL","semiScheduledGeneration":"1152.94","operationalDemand":"4054.94","rooftopSolar":"0","createdAt":"2021-02-11T20:32:41","wholesaleKWHPrice":"-0.9833999999999999","region":"VIC1","period":"2021-02-11T04:00:00","renewablesPercentage":"0.2843297311427543","periodSource":"30MIN","percentileRank":"0"},{"periodType":"ACTUAL","semiScheduledGeneration":"1123.31","operationalDemand":"4163.17","rooftopSolar":"0","createdAt":"2021-02-11T20:32:41","wholesaleKWHPrice":"1.2331","region":"VIC1","period":"2021-02-11T04:30:00","renewablesPercentage":"0.26982083364359366","periodSource":"30MIN","percentileRank":"0.17117117117117117"},{"periodType":"ACTUAL","semiScheduledGeneration":"1213.51","operationalDemand":"4311.58","rooftopSolar":"0","createdAt":"2021-02-11T20:32:41","wholesaleKWHPrice":"1.0384","region":"VIC1","period":"2021-02-11T05:00:00","renewablesPercentage":"0.28145366663728844","periodSource":"30MIN","percentileRank":"0.14414414414414414"},{"periodType":"ACTUAL","semiScheduledGeneration":"1257.4","operationalDemand":"4621.36","rooftopSolar":"0","createdAt":"2021-02-11T20:32:41","wholesaleKWHPrice":"2.5938","region":"VIC1","period":"2021-02-11T05:30:00","renewablesPercentage":"0.2720844080530407","periodSource":"30MIN","percentileRank":"0.26126126126126126"},{"periodType":"ACTUAL","semiScheduledGeneration":"1338.27","operationalDemand":"4849.08","rooftopSolar":"4.17","createdAt":"2021-02-11T20:32:41","wholesaleKWHPrice":"2.3683000000000005","region":"VIC1","period":"2021-02-11T06:00:00","renewablesPercentage":"0.2766063977746871","periodSource":"30MIN","percentileRank":"0.25225225225225223"},{"periodType":"ACTUAL","semiScheduledGeneration":"1439.58","operationalDemand":"5161.04","rooftopSolar":"62.23","createdAt":"2021-02-11T20:32:41","wholesaleKWHPrice":"3.168","region":"VIC1","period":"2021-02-11T06:30:00","renewablesPercentage":"0.2875229501825485","periodSource":"30MIN","percentileRank":"0.35135135135135137"},{"periodType":"ACTUAL","semiScheduledGeneration":"1581.59","operationalDemand":"5319.98","rooftopSolar":"181.004","createdAt":"2021-02-11T20:32:41","wholesaleKWHPrice":"3.3242000000000003","region":"VIC1","period":"2021-02-11T07:00:00","renewablesPercentage":"0.320414311330482","periodSource":"30MIN","percentileRank":"0.4144144144144144"},{"periodType":"ACTUAL","semiScheduledGeneration":"1649.09","operationalDemand":"5254.2","rooftopSolar":"288.601","createdAt":"2021-02-11T20:32:41","wholesaleKWHPrice":"2.992","region":"VIC1","period":"2021-02-11T07:30:00","renewablesPercentage":"0.3495869687546062","periodSource":"30MIN","percentileRank":"0.2882882882882883"},{"periodType":"ACTUAL","semiScheduledGeneration":"1765.18","operationalDemand":"5242.24","rooftopSolar":"512.975","createdAt":"2021-02-11T20:32:41","wholesaleKWHPrice":"1.8535000000000004","region":"VIC1","period":"2021-02-11T08:00:00","renewablesPercentage":"0.39584185821033624","periodSource":"30MIN","percentileRank":"0.2072072072072072"},{"periodType":"ACTUAL","semiScheduledGeneration":"1793.39","operationalDemand":"5290.37","rooftopSolar":"743.354","createdAt":"2021-02-11T20:32:41","wholesaleKWHPrice":"0.8646","region":"VIC1","period":"2021-02-11T08:30:00","renewablesPercentage":"0.4204275833631104","periodSource":"30MIN","percentileRank":"0.07207207207207207"},{"periodType":"ACTUAL","semiScheduledGeneration":"1849.65","operationalDemand":"5217.43","rooftopSolar":"942.579","createdAt":"2021-02-11T20:32:41","wholesaleKWHPrice":"0.9108","region":"VIC1","period":"2021-02-11T09:00:00","renewablesPercentage":"0.4532832663069162","periodSource":"30MIN","percentileRank":"0.08108108108108109"},{"periodType":"ACTUAL","semiScheduledGeneration":"1865.92","operationalDemand":"5248.98","rooftopSolar":"1113.67","createdAt":"2021-02-11T20:32:41","wholesaleKWHPrice":"0.23210000000000003","region":"VIC1","period":"2021-02-11T09:30:00","renewablesPercentage":"0.46829387126433175","periodSource":"30MIN","percentileRank":"0.036036036036036036"},{"periodType":"ACTUAL","semiScheduledGeneration":"1885.47","operationalDemand":"5343.02","rooftopSolar":"1239.545","createdAt":"2021-02-11T20:32:41","wholesaleKWHPrice":"1.6401","region":"VIC1","period":"2021-02-11T10:00:00","renewablesPercentage":"0.47474122929283646","periodSource":"30MIN","percentileRank":"0.1981981981981982"},{"periodType":"ACTUAL","semiScheduledGeneration":"1968.82","operationalDemand":"5461.42","rooftopSolar":"1307.89","createdAt":"2021-02-11T20:32:41","wholesaleKWHPrice":"-0.33660000000000007","region":"VIC1","period":"2021-02-11T10:30:00","renewablesPercentage":"0.48405376618887297","periodSource":"30MIN","percentileRank":"0.009009009009009009"},{"periodType":"ACTUAL","semiScheduledGeneration":"2061.17","operationalDemand":"5682.74","rooftopSolar":"1243.94","createdAt":"2021-02-11T20:32:41","wholesaleKWHPrice":"1.9844000000000002","region":"VIC1","period":"2021-02-11T11:00:00","renewablesPercentage":"0.47715644435718124","periodSource":"30MIN","percentileRank":"0.21621621621621623"},{"periodType":"ACTUAL","semiScheduledGeneration":"1993.97","operationalDemand":"5815.3","rooftopSolar":"1232.509","createdAt":"2021-02-11T20:32:41","wholesaleKWHPrice":"1.5532","region":"VIC1","period":"2021-02-11T11:30:00","renewablesPercentage":"0.4577988705426041","periodSource":"30MIN","percentileRank":"0.1891891891891892"},{"periodType":"ACTUAL","semiScheduledGeneration":"2077.11","operationalDemand":"6045.15","rooftopSolar":"1127.188","createdAt":"2021-02-11T20:32:41","wholesaleKWHPrice":"3.4539999999999997","region":"VIC1","period":"2021-02-11T12:00:00","renewablesPercentage":"0.44675780756567807","periodSource":"30MIN","percentileRank":"0.5135135135135135"},{"periodType":"ACTUAL","semiScheduledGeneration":"2010.35","operationalDemand":"6163.79","rooftopSolar":"1170.76","createdAt":"2021-02-11T20:32:41","wholesaleKWHPrice":"4.2119","region":"VIC1","period":"2021-02-11T12:30:00","renewablesPercentage":"0.43371576988363286","periodSource":"30MIN","percentileRank":"0.8558558558558559"},{"periodType":"ACTUAL","semiScheduledGeneration":"1912.94","operationalDemand":"6334","rooftopSolar":"1070.384","createdAt":"2021-02-11T20:32:41","wholesaleKWHPrice":"4.4990000000000006","region":"VIC1","period":"2021-02-11T13:00:00","renewablesPercentage":"0.40291319304887485","periodSource":"30MIN","percentileRank":"0.9099099099099099"},{"periodType":"ACTUAL","semiScheduledGeneration":"1875.1","operationalDemand":"6519.07","rooftopSolar":"1081.456","createdAt":"2021-02-11T20:32:41","wholesaleKWHPrice":"4.363700000000001","region":"VIC1","period":"2021-02-11T13:30:00","renewablesPercentage":"0.38899360386373255","periodSource":"30MIN","percentileRank":"0.8828828828828829"},{"periodType":"ACTUAL","semiScheduledGeneration":"1735.83","operationalDemand":"6641.06","rooftopSolar":"960.389","createdAt":"2021-02-11T20:32:41","wholesaleKWHPrice":"4.829","region":"VIC1","period":"2021-02-11T14:00:00","renewablesPercentage":"0.35469803191470467","periodSource":"30MIN","percentileRank":"0.918918918918919"},{"periodType":"ACTUAL","semiScheduledGeneration":"1645.13","operationalDemand":"6805.32","rooftopSolar":"866.817","createdAt":"2021-02-11T20:32:41","wholesaleKWHPrice":"4.1448","region":"VIC1","period":"2021-02-11T14:30:00","renewablesPercentage":"0.327411645542826","periodSource":"30MIN","percentileRank":"0.8198198198198198"},{"periodType":"ACTUAL","semiScheduledGeneration":"1636.65","operationalDemand":"7021.11","rooftopSolar":"718.162","createdAt":"2021-02-11T20:32:41","wholesaleKWHPrice":"4.2647","region":"VIC1","period":"2021-02-11T15:00:00","renewablesPercentage":"0.3042678949647977","periodSource":"30MIN","percentileRank":"0.8648648648648649"},{"periodType":"ACTUAL","semiScheduledGeneration":"1614.37","operationalDemand":"7092.7","rooftopSolar":"632.599","createdAt":"2021-02-11T20:32:41","wholesaleKWHPrice":"4.049100000000001","region":"VIC1","period":"2021-02-11T15:30:00","renewablesPercentage":"0.2908585156380355","periodSource":"30MIN","percentileRank":"0.7927927927927928"},{"periodType":"ACTUAL","semiScheduledGeneration":"1626.47","operationalDemand":"7133.5","rooftopSolar":"555.288","createdAt":"2021-02-11T20:32:41","wholesaleKWHPrice":"4.116200000000001","region":"VIC1","period":"2021-02-11T16:00:00","renewablesPercentage":"0.2837583764827434","periodSource":"30MIN","percentileRank":"0.8108108108108109"},{"periodType":"ACTUAL","semiScheduledGeneration":"1583","operationalDemand":"7064","rooftopSolar":"482.183","createdAt":"2021-02-11T20:32:41","wholesaleKWHPrice":"4.0920000000000005","region":"VIC1","period":"2021-02-11T16:30:00","renewablesPercentage":"0.27367253086759225","periodSource":"30MIN","percentileRank":"0.8018018018018018"},{"periodType":"ACTUAL","semiScheduledGeneration":"1493.37","operationalDemand":"7156.29","rooftopSolar":"371.711","createdAt":"2021-02-11T20:32:41","wholesaleKWHPrice":"4.2724","region":"VIC1","period":"2021-02-11T17:00:00","renewablesPercentage":"0.24775249099993477","periodSource":"30MIN","percentileRank":"0.8738738738738738"},{"periodType":"ACTUAL","semiScheduledGeneration":"1434.28","operationalDemand":"7067.67","rooftopSolar":"205.948","createdAt":"2021-02-11T20:32:41","wholesaleKWHPrice":"3.8137000000000008","region":"VIC1","period":"2021-02-11T17:30:00","renewablesPercentage":"0.22550373143049304","periodSource":"30MIN","percentileRank":"0.6846846846846847"},{"periodType":"ACTUAL","semiScheduledGeneration":"1021.5","operationalDemand":"7072.96","rooftopSolar":"114.136","createdAt":"2021-02-11T20:32:41","wholesaleKWHPrice":"5.1381000000000006","region":"VIC1","period":"2021-02-11T18:00:00","renewablesPercentage":"0.15801041199394023","periodSource":"30MIN","percentileRank":"0.9279279279279279"},{"periodType":"ACTUAL","semiScheduledGeneration":"903.91","operationalDemand":"6927.94","rooftopSolar":"55.418","createdAt":"2021-02-11T20:32:41","wholesaleKWHPrice":"5.6155","region":"VIC1","period":"2021-02-11T18:30:00","renewablesPercentage":"0.1373734527142959","periodSource":"30MIN","percentileRank":"0.9459459459459459"},{"periodType":"ACTUAL","semiScheduledGeneration":"796.68","operationalDemand":"6846.46","rooftopSolar":"16.081","createdAt":"2021-02-11T20:32:41","wholesaleKWHPrice":"6.514200000000001","region":"VIC1","period":"2021-02-11T19:00:00","renewablesPercentage":"0.11843441081080608","periodSource":"30MIN","percentileRank":"0.990990990990991"},{"periodType":"ACTUAL","semiScheduledGeneration":"800.63","operationalDemand":"6834.05","rooftopSolar":"0.974","createdAt":"2021-02-11T20:32:41","wholesaleKWHPrice":"5.899300000000001","region":"VIC1","period":"2021-02-11T19:30:00","renewablesPercentage":"0.11727888592636983","periodSource":"30MIN","percentileRank":"0.972972972972973"},{"periodType":"ACTUAL","semiScheduledGeneration":"671.62","operationalDemand":"6731.66","rooftopSolar":"0","createdAt":"2021-02-11T20:32:41","wholesaleKWHPrice":"5.8784","region":"VIC1","period":"2021-02-11T20:00:00","renewablesPercentage":"0.09977033896542606","periodSource":"30MIN","percentileRank":"0.963963963963964"},{"periodType":"ACTUAL","semiScheduledGeneration":"797.1","operationalDemand":"6484.81","rooftopSolar":"0","createdAt":"2021-02-11T20:32:41","wholesaleKWHPrice":"4.424200000000001","region":"VIC1","period":"2021-02-11T20:30:00","renewablesPercentage":"0.12291801918637554","periodSource":"30MIN","percentileRank":"0.8918918918918919"},{"period":"2021-02-11T21:00:00","periodType":"ACTUAL","periodSource":"5MIN","latestPeriod":"2021-02-11T20:35:00","region":"VIC1","rooftopSolar":"0","usage":"37871.52000","operationalDemand":"37871.52000","wholesaleKWHPrice":"5.35090","renewablesPercentage":"0.11660","percentileRank":"0.9369369369369369"},{"periodType":"FORECAST","semiScheduledGeneration":"774.883","operationalDemand":"5975.54","rooftopSolar":"0","forecastedAt":"2021-02-11T20:30:00","forecastedAt+period":"2021-02-11T20:30:00+2021-02-11T21:30:00","createdAt":"2021-02-11T20:32:41","wholesaleKWHPrice":"4.1970995","period":"2021-02-11T21:30:00","region":"VIC1","renewablesPercentage":"0.1296758117258022","periodSource":"30MIN","percentileRank":"0.8378378378378378"},{"periodType":"FORECAST","semiScheduledGeneration":"727.837","operationalDemand":"5690.44","rooftopSolar":"0","forecastedAt":"2021-02-11T20:30:00","forecastedAt+period":"2021-02-11T20:30:00+2021-02-11T22:00:00","createdAt":"2021-02-11T20:32:41","wholesaleKWHPrice":"3.8456","period":"2021-02-11T22:00:00","region":"VIC1","renewablesPercentage":"0.12790522349765573","periodSource":"30MIN","percentileRank":"0.6936936936936937"},{"periodType":"FORECAST","semiScheduledGeneration":"727.672","operationalDemand":"5424.17","rooftopSolar":"0","forecastedAt":"2021-02-11T20:30:00","forecastedAt+period":"2021-02-11T20:30:00+2021-02-11T22:30:00","createdAt":"2021-02-11T20:32:41","wholesaleKWHPrice":"3.8767487000000003","period":"2021-02-11T22:30:00","region":"VIC1","renewablesPercentage":"0.13415361244208793","periodSource":"30MIN","percentileRank":"0.7837837837837838"},{"periodType":"FORECAST","semiScheduledGeneration":"741.469","operationalDemand":"5250.52","rooftopSolar":"0","forecastedAt":"2021-02-11T20:30:00","forecastedAt+period":"2021-02-11T20:30:00+2021-02-11T23:00:00","createdAt":"2021-02-11T20:32:41","wholesaleKWHPrice":"3.8456","period":"2021-02-11T23:00:00","region":"VIC1","renewablesPercentage":"0.14121820314940234","periodSource":"30MIN","percentileRank":"0.7027027027027027"},{"periodType":"FORECAST","semiScheduledGeneration":"745.414","operationalDemand":"5253.06","rooftopSolar":"0","forecastedAt":"2021-02-11T20:30:00","forecastedAt+period":"2021-02-11T20:30:00+2021-02-11T23:30:00","createdAt":"2021-02-11T20:32:41","wholesaleKWHPrice":"3.8456","period":"2021-02-11T23:30:00","region":"VIC1","renewablesPercentage":"0.14190091108801345","periodSource":"30MIN","percentileRank":"0.7117117117117117"},{"periodType":"FORECAST","semiScheduledGeneration":"758.676","operationalDemand":"5157.49","rooftopSolar":"0","forecastedAt":"2021-02-11T20:30:00","forecastedAt+period":"2021-02-11T20:30:00+2021-02-12T00:00:00","createdAt":"2021-02-11T20:32:41","wholesaleKWHPrice":"3.8456","period":"2021-02-12T00:00:00","region":"VIC1","renewablesPercentage":"0.14710178788519224","periodSource":"30MIN","percentileRank":"0.7207207207207207"},{"periodType":"FORECAST","semiScheduledGeneration":"777.302","operationalDemand":"4949.95","rooftopSolar":"0","forecastedAt":"2021-02-11T20:30:00","forecastedAt+period":"2021-02-11T20:30:00+2021-02-12T00:30:00","createdAt":"2021-02-11T20:32:41","wholesaleKWHPrice":"3.8456","period":"2021-02-12T00:30:00","region":"VIC1","renewablesPercentage":"0.15703229325548743","periodSource":"30MIN","percentileRank":"0.7297297297297297"},{"periodType":"FORECAST","semiScheduledGeneration":"789.727","operationalDemand":"4764.39","rooftopSolar":"0","forecastedAt":"2021-02-11T20:30:00","forecastedAt+period":"2021-02-11T20:30:00+2021-02-12T01:00:00","createdAt":"2021-02-11T20:32:41","wholesaleKWHPrice":"3.7587539000000008","period":"2021-02-12T01:00:00","region":"VIC1","renewablesPercentage":"0.1657561618591257","periodSource":"30MIN","percentileRank":"0.6576576576576577"},{"periodType":"FORECAST","semiScheduledGeneration":"756.021","operationalDemand":"4613.41","rooftopSolar":"0","forecastedAt":"2021-02-11T20:30:00","forecastedAt+period":"2021-02-11T20:30:00+2021-02-12T01:30:00","createdAt":"2021-02-11T20:32:41","wholesaleKWHPrice":"3.707256300000001","period":"2021-02-12T01:30:00","region":"VIC1","renewablesPercentage":"0.16387466104248266","periodSource":"30MIN","percentileRank":"0.6306306306306306"},{"periodType":"FORECAST","semiScheduledGeneration":"721.964","operationalDemand":"4482.2","rooftopSolar":"0","forecastedAt":"2021-02-11T20:30:00","forecastedAt+period":"2021-02-11T20:30:00+2021-02-12T02:00:00","createdAt":"2021-02-11T20:32:41","wholesaleKWHPrice":"3.6572954","period":"2021-02-12T02:00:00","region":"VIC1","renewablesPercentage":"0.1610735799384231","periodSource":"30MIN","percentileRank":"0.6216216216216216"},{"periodType":"FORECAST","semiScheduledGeneration":"684.434","operationalDemand":"4362.74","rooftopSolar":"0","forecastedAt":"2021-02-11T20:30:00","forecastedAt+period":"2021-02-11T20:30:00+2021-02-12T02:30:00","createdAt":"2021-02-11T20:32:41","wholesaleKWHPrice":"3.6158254","period":"2021-02-12T02:30:00","region":"VIC1","renewablesPercentage":"0.15688168444601328","periodSource":"30MIN","percentileRank":"0.6036036036036037"},{"periodType":"FORECAST","semiScheduledGeneration":"652.779","operationalDemand":"4317.93","rooftopSolar":"0","forecastedAt":"2021-02-11T20:30:00","forecastedAt+period":"2021-02-11T20:30:00+2021-02-12T03:00:00","createdAt":"2021-02-11T20:32:41","wholesaleKWHPrice":"3.5780855000000003","period":"2021-02-12T03:00:00","region":"VIC1","renewablesPercentage":"0.15117868978885715","periodSource":"30MIN","percentileRank":"0.5855855855855856"},{"periodType":"FORECAST","semiScheduledGeneration":"616.72","operationalDemand":"4305.99","rooftopSolar":"0","forecastedAt":"2021-02-11T20:30:00","forecastedAt+period":"2021-02-11T20:30:00+2021-02-12T03:30:00","createdAt":"2021-02-11T20:32:41","wholesaleKWHPrice":"3.5748130000000002","period":"2021-02-12T03:30:00","region":"VIC1","renewablesPercentage":"0.14322374181082634","periodSource":"30MIN","percentileRank":"0.5765765765765766"}]},"message":""}'
  
    // Electricity price is calculated as follows:
    // data.staticPrices.E1.totalfixedKWHPrice + data.staticPrices.E1.lossFactor * data.variablePricesAndRenewables.[period].wholesaleKWHPrice
    
    var Number totalFixedKWHPrice = Float::parseFloat(transform("JSONPATH", "$.data.staticPrices.E1.totalfixedKWHPrice", response_json))
    var Number lossFactor = Float::parseFloat(transform("JSONPATH", "$.data.staticPrices.E1.lossFactor", response_json))
    var String rawjson = transform("JSONPATH", "$.data.variablePricesAndRenewables", response_json)
    
    logInfo(filename, "totalFixedKWHPrice = " + String::format("%s", totalFixedKWHPrice))
    logInfo(filename, "lossFactor = " + String::format("%s", lossFactor))

   // OpenHAB forum proposed solution
    var forecastWS="none found" // result placeholders
    var forecastStamp = "-"

    // get string results like ["2020-10-18T22:00:00", "2020-10-18T22:30:00", "2020-10-18T23:00:00"] - note these are not arrays
    var String PDresults = transform("JSONPATH", "$.[?(@.periodType=='FORECAST')].period", rawjson)
    logInfo(filename, "PDresults = " + String::format("%s", PDresults))
    //--> This returns NULL

    // get string results like ["4.5254957000000005", "4.8887993000000005", "4.3353760999999995"] - note these are not arrays
    var String WSresults = transform("JSONPATH", "$.[?(@.periodType=='FORECAST')].wholesaleKWHPrice", rawjson)
    logInfo(filename, "WSresults = " + String::format("%s", WSresults))   
    //--> This returns NULL

    if (PDresults !== null && WSresults !== null) { // check found some
        PDresults = PDresults.replace("[","").replace("]","")
        val PDlist = PDresults.split(",")
        WSresults = WSresults.replace("[","").replace("]","")
        val WSlist = WSresults.split(",")
            // now as Lists
        val masterMap = new HashMap<String,String>()
        for (var int i = 0; i < PDlist.size ; i++) {
            masterMap.put(PDlist.get(i),WSlist.get(i))  // key-value
        }
        // all that to load hashmap, must be a more elegant way!
        val sortedKeys=new ArrayList(masterMap.keySet())
        Collections::sort(sortedKeys)  //just sort keys as strings works okay
        forecastWS = masterMap.get(sortedKeys.last) // use bottom key for oldest
        forecastStamp = sortedKeys.last
    }
    logInfo(filename, "Oldest forecast - " + forecastWS + " on " + forecastStamp )

//    power_price.postUpdate(calc_price)

end 

Any thoughts on why I am getting NULL for PDresults and WSresults?
Thanks again for your help.

I always have a hope declaring val is somehow more efficient than var if we do not intend to change it, don’t know if that is so.
val does have other uses where you can pass to lambdas.

“arrays” are a bit funny, you can declare the whole object as val but are allowed to change the contents.

1 Like

I had to add [ ] to the JSON extract you showed for testing, it all rather depends on your real overall JSON structure. I could not find a way to deal with “{xx},{xx}” structure only. I expect there is one.

The openHAB JSONPATH transformation service is not JSONPATH, and can be pickier about terms that validators accept. It has limitations, like can only return a single string.

This is great, thank you! I’m a step further thanks to your help, just have one more challenge to overcome (turns out I need what is called natural sorting I think in this case), and then I’ll post my solution. Appreciate the help!

So I’ve run into a problem related to datatypes which I suspect is fairly simple, but I just haven’t been able to work it out, due to my limited understanding of java, objects, hashmaps and arraylists…

In the following code, I am trying to get the ‘wholesaleKWhPrice’ as a float value. After sorting and extracting a value from the arraylist, it is a single value, which I thought was a string value, but my ‘instanceof’ checks tells me it is an object. How do I convert this (string) object to a float value?

// This rule retrieves the actual wholesale electricity prices 

import java.util.HashMap
import java.util.ArrayList
import java.util.Collections

val String filename = "power_price.rules"

rule "Get wholesale power price"
when
    System started or
    Time cron "0 0/1 * 1/1 * ? * "    // or trigger every 30 minutes of every day starting on the 1st of each month (1 min for testing)
    
then
    val String URL = "https://api.XXX"
    val String contenttype = "application/json"
    var String jsondata = '{ "networkName":' + ' "YYY }' r

    logInfo(filename, "Sending API request...")

    var String response_json = sendHttpPostRequest(URL, contenttype, jsondata)
    
    var Number totalFixedKWhPrice = Float::parseFloat(transform("JSONPATH", "$.data.staticPrices.E1.totalfixedKWHPrice", response_json))
    var Number lossFactor = Float::parseFloat(transform("JSONPATH", "$.data.staticPrices.E1.lossFactor", response_json))
    
// OpenHAB forum proposed solution
    var String recentPrice = "none found" // result placeholders
    var String forecastStamp = "-"

    // Get string results like ["2020-10-18T22:00:00", "2020-10-18T22:30:00", "2020-10-18T23:00:00"] - note these are not arrays
    var String PDresults = transform("JSONPATH", "$.data.variablePricesAndRenewables.[?(@.periodType=='ACTUAL')].[?(@.periodSource=='30MIN')].period", response_json)
    logInfo(filename, "PDresults = " + String::format("%s", PDresults))

    // Get string results like ["4.5254957000000005", "4.8887993000000005", "4.3353760999999995"] - note these are not arrays
    var String WSresults = transform("JSONPATH", "$.data.variablePricesAndRenewables.[?(@.periodType=='ACTUAL')].[?(@.periodSource=='30MIN')].wholesaleKWHPrice", response_json)
    logInfo(filename, "WSresults = " + String::format("%s", WSresults))   

    if (PDresults !== null && WSresults !== null) { // check found some
        PDresults = PDresults.replace("[","").replace("]","")
        val PDlist = PDresults.split(",")
        WSresults = WSresults.replace("[","").replace("]","")
        val WSlist = WSresults.split(",")
        
        // Now as Lists
        val masterMap = new HashMap<String,String>()
        for (var int i = 0; i < PDlist.size ; i++) {
            masterMap.put(PDlist.get(i),WSlist.get(i))  // key-value
        }

        // Load hashmap
        val sortedKeys=new ArrayList(masterMap.keySet())
        Collections::sort(sortedKeys)  //just sort keys as strings works okay -> Not working properly, check out 'sortAsNumbers'
        logInfo(filename, "sortedKeys = " + String::format("%s", sortedKeys))  
        recentPrice = masterMap.get(sortedKeys.get(PDlist.size - 2)).replace("\"", "") // use 2nd last key for most recent and remove one set of double quotes (seems to have 2)
        forecastStamp = sortedKeys.get(PDlist.size - 2)     // get timestamp of most recent value

        // Check type of recentPrice (log returns 'Object')
        if(recentPrice instanceof DecimalType) logInfo(filename,"It's a 'Number'")
        if(recentPrice instanceof StringType) logInfo(filename,"It's a 'String'")
        if(recentPrice instanceof Object) logInfo(filename,"It's an 'Object'")
        
        // Various attempts to convert 'wholesaleKWhPrice' to a float value
        // var Number wholesaleKWhPrice = Float::parseFloat(String::format("%s", recentPrice)) --> Log: Error during the execution of rule 'Get wholesale power price': The name 'wholesaleKWhPrice' cannot be resolved to an item or type
        // var Number wholesaleKWhPrice = recentPrice.toFloat --> Log: rror during the execution of startup rule 'Get wholesale power price': 'toFloat' is not a member of 'java.lang.String'
        // var Number wholesaleKWhPrice = Float::parseFloat(recentPrice.state) --> Log: Error during the execution of rule 'Get wholesale power price': 'state' is not a member of 'java.lang.String'
        }
    
    logInfo(filename, "Newest price:" + recentPrice + " on " + forecastStamp )
    logInfo(filename, "totalFixedKWhPrice = " + String::format("%s", totalFixedKWhPrice))
    logInfo(filename, "lossFactor = " + String::format("%s", lossFactor))
    logInfo(filename, "wholesaleKWHPrice = " + String::format("%s", wholesaleKWhPrice))

    var Number calc_price = totalFixedKWhPrice + lossFactor * wholesaleKWhPrice
    power_price.postUpdate(calc_price)

end

Given that ‘totalFixedKWhPrice’ and ‘lossFactor’ work fine, I suspect that the conversion of the JSON string value into a hasmap and then an arraylist has changed it’s type, but would appreciate any help on why this happens and how to fix it!

Well, we loaded masterMap with strings, and here you’ve done a successful string .replace method on the element.
Your error messages tell you it is java.lang.String

This attempt looks just fine to me, albeit the format serves no purpose.

If it won’t parse, maybe the string contains something unexpected.

This error message-

does not come from your parsing attempt.

It’s from later, where you attempt to use it.

It’s a string, all your error messages tell you that:


This error is probably because you tested the rule with this commented out. It means there is no variable called wholesaleKWhPrice declared.

// var Number wholesaleKWhPrice = Float::parseFloat(String::format("%s", recentPrice)) --> Log: Error during the execution of rule 'Get wholesale power price': The name 'wholesaleKWhPrice' cannot be resolved to an item or type

'toFloat' is not a member of 'java.lang.String' tells you that recentPrice is a string.

// var Number wholesaleKWhPrice = recentPrice.toFloat --> Log: rror during the execution of startup rule 'Get wholesale power price': 'toFloat' is not a member of 'java.lang.String'

Again telling you that the method you are trying to use is not a member of java.lang.String

// var Number wholesaleKWhPrice = Float::parseFloat(recentPrice.state) --> Log: Error during the execution of rule 'Get wholesale power price': 'state' is not a member of 'java.lang.String'

The answer is staring you in the face, you use this method all over this rule:

var wholesaleKWhPrice = Float::parseFloat(recentPrice)

Well, this is what I tried initially, given that it works successfully on extraction of the ‘totalFixedKWhPrice’ and the ‘lossFactor’, but it does not work - ‘… The name ‘wholesaleKWhPrice’ cannot be resolved to an item or type’.

Of course I tested each of these lines separately, without the comments.
So I still think the string value is not being parsed to a float.

As you pointed out, this doesn’t come from the parsing attempt, but later when I try to use it. However, if I comment out all lines that use ‘wholesaleKWhPrice’, apart from the log info line:

logInfo(filename, "wholesaleKWHPrice = " + String::format("%s", wholesaleKWhPrice))

I still get the error, which led me to believe there was no value coming out of the parse atempt. So I think this error will be resolved once the conversion to float takes place.

Attempting to parse the string value using

var Number wholesaleKWhPrice = Float::parseFloat(recentPrice)

gives a log output as follows:

==> /var/log/openhab2/openhab.log <==
2021-03-05 20:57:00.010 [INFO ] [thome.model.script.power_price.rules] - Sending API request...
2021-03-05 20:57:00.250 [INFO ] [thome.model.script.power_price.rules] - PDresults = ["2021-03-04T20:30:00", "2021-03-04T21:00:00", "2021-03-04T21:30:00", "2021-03-04T22:00:00", "2021-03-04T22:30:00", "2021-03-04T23:00:00", "2021-03-04T23:30:00", "2021-03-05T00:00:00", "2021-03-05T00:30:00", "2021-03-05T01:00:00", "2021-03-05T01:30:00", "2021-03-05T02:00:00", "2021-03-05T02:30:00", "2021-03-05T03:00:00", "2021-03-05T03:30:00", "2021-03-05T04:00:00", "2021-03-05T04:30:00", "2021-03-05T05:00:00", "2021-03-05T05:30:00", "2021-03-05T06:00:00", "2021-03-05T06:30:00", "2021-03-05T07:00:00", "2021-03-05T07:30:00", "2021-03-05T08:00:00", "2021-03-05T08:30:00", "2021-03-05T09:00:00", "2021-03-05T09:30:00", "2021-03-05T10:00:00", "2021-03-05T10:30:00", "2021-03-05T11:00:00", "2021-03-05T11:30:00", "2021-03-05T12:00:00", "2021-03-05T12:30:00", "2021-03-05T13:00:00", "2021-03-05T13:30:00", "2021-03-05T14:00:00", "2021-03-05T14:30:00", "2021-03-05T15:00:00", "2021-03-05T15:30:00", "2021-03-05T16:00:00", "2021-03-05T16:30:00", "2021-03-05T17:00:00", "2021-03-05T17:30:00", "2021-03-05T18:00:00", "2021-03-05T18:30:00", "2021-03-05T19:00:00", "2021-03-05T19:30:00", "2021-03-05T20:00:00"]
2021-03-05 20:57:00.267 [INFO ] [thome.model.script.power_price.rules] - WSresults = ["3.2648", "3.0327", "2.7401000000000004", "2.2242", "3.1449000000000003", "3.1823000000000006", "3.2043", "3.3", "3.2516", "3.2064999999999997", "2.805", "2.7555000000000005", "3.0811", "2.7664999999999997", "2.9117", "3.1768", "3.0712", "3.1130000000000004", "3.3165", "3.8808000000000007", "4.0876", "3.8005000000000004", "2.9359000000000006", "2.7500000000000004", "2.2605000000000004", "1.2100000000000002", "2.2", "1.8678000000000001", "1.8062000000000005", "1.6852", "1.0967000000000002", "0.8634999999999999", "0.8535999999999999", "0.9735000000000001", "0.7865000000000001", "0.5940000000000001", "0.5753000000000001", "0.6072", "1.0164000000000002", "2.0867", "2.0911000000000004", "2.3683000000000005", "2.7445", "2.9227000000000003", "3.2835", "1.9756000000000005", "3.3209000000000004", "3.3"]
2021-03-05 20:57:00.387 [INFO ] [thome.model.script.power_price.rules] - sortedKeys = [ "2021-03-04T21:00:00",  "2021-03-04T21:30:00",  "2021-03-04T22:00:00",  "2021-03-04T22:30:00",  "2021-03-04T23:00:00",  "2021-03-04T23:30:00",  "2021-03-05T00:00:00",  "2021-03-05T00:30:00",  "2021-03-05T01:00:00",  "2021-03-05T01:30:00",  "2021-03-05T02:00:00",  "2021-03-05T02:30:00",  "2021-03-05T03:00:00",  "2021-03-05T03:30:00",  "2021-03-05T04:00:00",  "2021-03-05T04:30:00",  "2021-03-05T05:00:00",  "2021-03-05T05:30:00",  "2021-03-05T06:00:00",  "2021-03-05T06:30:00",  "2021-03-05T07:00:00",  "2021-03-05T07:30:00",  "2021-03-05T08:00:00",  "2021-03-05T08:30:00",  "2021-03-05T09:00:00",  "2021-03-05T09:30:00",  "2021-03-05T10:00:00",  "2021-03-05T10:30:00",  "2021-03-05T11:00:00",  "2021-03-05T11:30:00",  "2021-03-05T12:00:00",  "2021-03-05T12:30:00",  "2021-03-05T13:00:00",  "2021-03-05T13:30:00",  "2021-03-05T14:00:00",  "2021-03-05T14:30:00",  "2021-03-05T15:00:00",  "2021-03-05T15:30:00",  "2021-03-05T16:00:00",  "2021-03-05T16:30:00",  "2021-03-05T17:00:00",  "2021-03-05T17:30:00",  "2021-03-05T18:00:00",  "2021-03-05T18:30:00",  "2021-03-05T19:00:00",  "2021-03-05T19:30:00",  "2021-03-05T20:00:00", "2021-03-04T20:30:00"]
2021-03-05 20:57:00.393 [INFO ] [thome.model.script.power_price.rules] - It's an 'Object'
2021-03-05 20:57:00.397 [INFO ] [thome.model.script.power_price.rules] - Newest price: 3.3 on  "2021-03-05T20:00:00"
2021-03-05 20:57:00.400 [INFO ] [thome.model.script.power_price.rules] - totalFixedKWhPrice = 11.415
2021-03-05 20:57:00.404 [INFO ] [thome.model.script.power_price.rules] - lossFactor = 1.0523916
2021-03-05 20:57:00.407 [ERROR] [ntime.internal.engine.ExecuteRuleJob] - Error during the execution of rule 'Get wholesale power price': The name 'wholesaleKWhPrice' cannot be resolved to an item or type; line 79, column 69, length 17

As you can see, there is a value (3.3), it’s a string, but it is not being converted to float and assigned to ‘wholesaleKWhPrice’. Any other ideas what I might be doing wrong?

It still doesn’t looklike a parse failure really, but let’s pry a bit closer.
Do away with ‘Number’ in var, to be frank DSL pretty much ignores that anyway.

logInfo("test", "looks like +" + recentPrice + "+")
logInfo("test", "char count " + recentPrice.length)
var wholesaleKWhPrice = Float::parseFloat(recentPrice)
logInfo("test", "past parse")
logInfo("test", "result " + wholesaleKWhPrice.toString)
// force an error to reveal type
logInfo("test", "blowup " + wholesaleKWhPrice.bananas)
2 Likes

This is great, I like your approach! Adding your proposed code gave me the following output:

2021-03-06 23:06:54.691 [INFO ] [thome.model.script.power_price.rules] - It's an 'Object'
2021-03-06 23:06:54.694 [INFO ] [.eclipse.smarthome.model.script.test] - looks like + 3.2252+
2021-03-06 23:06:54.698 [INFO ] [.eclipse.smarthome.model.script.test] - char count 7
2021-03-06 23:06:54.701 [INFO ] [.eclipse.smarthome.model.script.test] - past parse
2021-03-06 23:06:54.704 [INFO ] [.eclipse.smarthome.model.script.test] - result 3.2252
2021-03-06 23:06:54.708 [ERROR] [ntime.internal.engine.RuleEngineImpl] - Error during the execution of startup rule 'Get wholesale power price': 'bananas' is not a member of 'float'

Definitely a float! Following that, I thought I would see what it looks like using my original variation of:

var Number wholesaleKWhPrice = Float::parseFloat(recentPrice)

which produced:

2021-03-06 23:06:02.799 [INFO ] [thome.model.script.power_price.rules] - It's an 'Object'
2021-03-06 23:06:02.803 [INFO ] [.eclipse.smarthome.model.script.test] - looks like + 3.2252+
2021-03-06 23:06:02.806 [INFO ] [.eclipse.smarthome.model.script.test] - char count 7
2021-03-06 23:06:02.808 [INFO ] [.eclipse.smarthome.model.script.test] - past parse
2021-03-06 23:06:02.812 [INFO ] [.eclipse.smarthome.model.script.test] - result 3.2252
2021-03-06 23:06:02.815 [ERROR] [ntime.internal.engine.RuleEngineImpl] - Error during the execution of startup rule 'Get wholesale power price': 'bananas' is not a member of 'java.lang.Number'

However, using your code and it definitely being a float value, I was still getting the error

The name 'wholesaleKWhPrice' cannot be resolved to an item or type

which, together with your suspicion that it wasn’t a parse failure, made me wonder what was different about

logInfo(filename, "wholesaleKWHPrice = " + String::format("%s", wholesaleKWhPrice))

compared to the rest of the code. The only real difference was that it was outside of the ‘If’ statement to check for null values of ‘PDresults’ and ‘WSresults’. I then moved it inside of the if statement, together with the final calculation of the price, and viola!, it works! So, the last bit of the code, after some clean up, now looks like this:

    if (PDresults !== null && WSresults !== null) { // check found some
        PDresults = PDresults.replace("[","").replace("]","")
        val PDlist = PDresults.split(",")
        WSresults = WSresults.replace("[","").replace("]","")
        val WSlist = WSresults.split(",")
        
        // Now as Lists
        val masterMap = new HashMap<String,String>()
        for (var int i = 0; i < PDlist.size ; i++) {
            masterMap.put(PDlist.get(i),WSlist.get(i))  // key-value
        }

        // Load hashmap
        val sortedKeys=new ArrayList(masterMap.keySet())
        Collections::sort(sortedKeys)  //just sort keys as strings works okay
        logInfo(filename, "sortedKeys = " + String::format("%s", sortedKeys))  
        recentPrice = masterMap.get(sortedKeys.get(PDlist.size - 2)).replace("\"", "") // use 2nd last key for most recent and remove one set of double quotes (seems to have 2)
        forecastStamp = sortedKeys.get(PDlist.size - 2)     // get timestamp of most recent value

        logInfo(filename, "Newest price:" + recentPrice + " on " + forecastStamp     
        logInfo(filename, "totalFixedKWhPrice = " + String::format("%s", totalFixedKWhPrice))
        logInfo(filename, "lossFactor = " + String::format("%s", lossFactor))
        logInfo(filename, "wholesaleKWHPrice = " + String::format("%s", wholesaleKWhPrice))

        var Number calc_price = totalFixedKWhPrice + lossFactor * wholesaleKWhPrice
        logInfo(filename, "Calculated price = " + String::format("%s", calc_price))
        power_price.postUpdate(calc_price)
  }

So that begs the question, is a variable created within an ‘If’ statement only local to that If statement and not available outside of it?

By the way, the code also works using var Number such as

var Number (wholesaleKWhPrice / lossFactor / totalFixedKWhPrice)

for each of the elements to calculate the price, but I will use your version from now on.
Thank you for the help and patience with this, it has been invaluable!

Yes, I’d missed that. It’s actually local to the { } block of code. You can of course declare a variable before if(), outside the { }, modify it inside, and use it afterwards.

For anyone that might come across this post, I wanted to share my complete rule for deconstructing the JSON data to determine the wholesale power price:

// This rule retrieves the actual & forecast wholesale electricity prices, based on access to their API

import java.util.HashMap
import java.util.ArrayList
import java.util.Collections

val String filename = "power_price.rules"

rule "Get wholesale power price"
when
    System started or                   // Trigger when system has started or
    Time cron "0 4/10 * 1/1 * ? *"      // Trigger every 10 minutes of every day starting at 4 mins past the hour
    
then
    val String URL = "https://XXXXXXXX"
    val String contenttype = "application/json"
    var String jsondata = '{ "networkName":' + ' "YY" }' 
    var String response_json

    logInfo(filename, "Sending API request...")
    response_json = sendHttpPostRequest(URL, contenttype, jsondata, 10000)

    if (response_json !== null) {
        // As per info provided, price is calculated as follows:
        // data.staticPrices.E1.totalfixedKWHPrice + data.staticPrices.E1.lossFactor * data.variablePricesAndRenewables.[period].wholesaleKWHPrice
        var totalFixedKWhPrice = Float::parseFloat(transform("JSONPATH", "$.data.staticPrices.E1.totalfixedKWHPrice", response_json))
        var lossFactor = Float::parseFloat(transform("JSONPATH", "$.data.staticPrices.E1.lossFactor", response_json))

        // Get string results like ["2020-10-18T22:00:00", "2020-10-18T22:30:00", "2020-10-18T23:00:00"] - note these are not arrays
        var String WS_timestamps = transform("JSONPATH", "$.data.variablePricesAndRenewables.[?(@.periodType=='ACTUAL')].[?(@.periodSource=='30MIN')].period", response_json)
        logInfo(filename, "WS_timestamps = " + String::format("%s", WS_timestamps))

        // Get string results like ["4.5254957000000005", "4.8887993000000005", "4.3353760999999995"] - note these are not arrays
        var String WS_prices = transform("JSONPATH", "$.data.variablePricesAndRenewables.[?(@.periodType=='ACTUAL')].[?(@.periodSource=='30MIN')].wholesaleKWHPrice", response_json)
        logInfo(filename, "WS_prices = " + String::format("%s", WS_prices))   

        // Check that some data was obtained
        //if (WS_timestamps !== null && WS_prices !== null) {
        WS_timestamps = WS_timestamps.replace("[","").replace("]","")
        var WS_timestamp_list = WS_timestamps.split(",")
        WS_prices = WS_prices.replace("[","").replace("]","")
        var WS_prices_list = WS_prices.split(",")
        
        // Now as Lists
        var masterMap = new HashMap<String,String>()
        for (var int i = 0; i < WS_timestamp_list.size ; i++) {
            masterMap.put(WS_timestamp_list.get(i),WS_prices_list.get(i))  // key-value
        }

        // Load hashmap
        var sortedKeys=new ArrayList(masterMap.keySet())
        Collections::sort(sortedKeys)  //just sort keys as strings
        logInfo(filename, "sortedKeys = " + String::format("%s", sortedKeys))  
        var String recentPrice = masterMap.get(sortedKeys.get(WS_timestamp_list.size - 2)).replace("\"", "") // use 2nd last key for most recent and remove one set of double quotes (seems to have 2)
        var wholesaleKWhPrice = Float::parseFloat(recentPrice)
        logInfo(filename, "wholesaleKWhPrice = " + String::format("%s", wholesaleKWhPrice))

        var calc_price = totalFixedKWhPrice + lossFactor * wholesaleKWhPrice
        logInfo(filename, "Calculated price = " + String::format("%s", calc_price))
        power_price_actual.postUpdate(calc_price)
    }

    else {
        var calc_price = NULL
        logInfo(filename, "Power price could not be determined. No data.")
        power_price_actual.postUpdate(calc_price)
    }

end 
1 Like