Sort & select elements from JSON string within rules

[flix (Felix Liebrich)]

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