UPDATE v1.0.0 now on Github February 2024
The original Node-RED flow is still available but an update is now on Github for anyone who is interested. See post 97 at Octopus Agile - display tariff in graphs & tables, best import/export periods - all done using Node-RED and JSONata - #97 by Biscuit
Introduction
This project focuses on reading and processing UK Octopus Agile electricity tariff prices (both import and export) using Node-RED, with JSONata in change nodes as an alternative to JavaScript in function nodes. I have a working Node-RED flow, together with the necessary settings for graph and table card for HA display.
Disclaimers
The code has been tested (against JSONata v2.0.0) and is working for me at the current time.
Background
I am a fan of Node-RED for its no-code simplicity and speed of prototype / development. I have several flows (running 24/7 in Node-RED as an HA addon) used to monitor and measure solar PV, inverter, battery, and energy performance. I am with Octopus Energy for fixed price electricity, consumption as well as excess solar export, and I read and process my historic meter readings for daily, monthly, and yearly summaries. As a recent exercise, I am attempting to fully use JSONata in place of function nodes and JavaScript.
I have found JSONata quite challenging. JSONata is designed to query and process (select, map, transform, aggregate) JSON structures, and it does this incredibility well. Node-RED can become messy when attempting iteration using split/join nodes, so I use for-loops in JavaScript within a function node. JSONata is a revelation, as arrays and complex objects can be iterated over as part of a functional expression, and with little code required.
Octopus Agile
There are several integrations for Octopus (Agile) already. However, this is written in Node-RED, and therefore may be of use for anyone wanting to do something that they cannot achieve elsewhere. It is also offered as a practical example of how to use JSONata. I have found limited information in my searches for JSONata examples and tutorials, so I hope that this is of benefit to anyone wanting to explore and experiment with JSONata.
My aim in this project is to:
- Read the Octopus import and export agile tariff prices, once per day
- Display these prices on a graph in HA, covering both today and tomorrow
- Provide sensors in HA with current and next prices, updated every half hour
- Extract a set of ‘best-price slots’ for the (15) lowest-import / highest-export periods
- Group these ‘best slots’ into sets of longer contiguous periods (sequences)
Get paid for using electricity to charge the battery, and then sell it back later the same day for 24 p per kWh more!
Octopus Agile API – reading the tariff
Octopus offer (authorised) API access to customer’s own meter readings. The tariff products and prices are available without restriction.
More about Octopus open API and the Agile API documentation: -
https://developer.octopus.energy/docs/api/#agile-octopus
The API call I use: -
https://api.octopus.energy/v1/products/AGILE-22-08-31/electricity-tariffs/E-1R-AGILE-22-08-31-L/standard-unit-rates/?page_size=96
The key elements in the API call above are the tariff name ‘AGILE-22-08-31’ which I believe is the most recent offering. Other (older) tariff products are available. The ‘L’ is for my region (South-West) here in the UK. The regions are explained here: -
https://www.energy-stats.uk/dno-region-codes-explained/
The ‘?page_size=96’ is an option I use to pull 96 records rather than the default 100. Octopus publish the entire price tariff history, and every day add the next 24 hours (48 records) worth on the front end. This update happens reliably around 16:00. The price day runs from the 23:00-23:30 period on the same day to 22:30-23:00 for tomorrow (11pm to 11pm due to being priced on CET time zone, 1 hour ahead of the UK on GMT equivalent to UTC).
Each API call retrieves an array of objects, most recent first, with each object being
{"value_exc_vat":25.07,
"value_inc_vat":26.3235,
"valid_from":"2022-12-19T22:30:00Z",
"valid_to":"2022-12-19T23:00:00Z"}
Time appears to be always given as UTC Zulu timezone, so at daylight saving changes the tariff records remain contiguous and the user has to adjust or stay on UTC.
Node-RED flow
This is the Node-RED flow I use to obtain, process, store, and uplift to HA the tariff array, current and next prices, best price periods, and concurrent sequences.
EDIT 25 January 2024:
The Node-RED flow below has been modified to remove the state class ‘measurement’ from the configuration nodes.
[{"id":"e42010beae8f7fa5","type":"group","z":"1cc7d2e94a4815fe","name":"Octopus Agile Prices","style":{"label":true,"stroke":"#6f2fa0","fill":"#dbcbe7","color":"#001f60"},"nodes":["db6ebccc4bb88b8d","63bba87f93e0eaae","65094b21c3938d73","22fdc0bfd7bdd329","3c0e6412766b7286","0ba1d9b378092267","416e22ecfcbf5616","ce579e86db6fcc9b","be04e63b7258b035","4430da6cfe290e39","3797b648d315c3a1","bc4c506918ed4d9e","b98e955f8996efa2","c63c0e9f64999a1e","a28359905d33b762","59f0bbc0e3c76b0d","aa94392fea62febc","87bddd29d5e5ab90","224dc5d34ebe8967","8a65a4744d5f228a","3c01c3e92c0898b2","03b0c5c88010997c","339092c8ec953466","d8bb3057287698de","a92b801c291d33d0","56dd710454ea2e25","ee45b426f60a08a0","a9942de6076269cf"],"x":94,"y":139,"w":982,"h":462},{"id":"db6ebccc4bb88b8d","type":"http request","z":"1cc7d2e94a4815fe","g":"e42010beae8f7fa5","name":"Octopus Export","method":"GET","ret":"obj","paytoqs":"ignore","url":"https://api.octopus.energy/v1/products/AGILE-OUTGOING-19-05-13/electricity-tariffs/E-1R-AGILE-OUTGOING-19-05-13-L/standard-unit-rates/?page_size=96","tls":"","persist":false,"proxy":"","insecureHTTPParser":false,"authType":"","senderr":false,"headers":[],"x":380,"y":260,"wires":[["65094b21c3938d73"]]},{"id":"63bba87f93e0eaae","type":"http request","z":"1cc7d2e94a4815fe","g":"e42010beae8f7fa5","name":"Octopus Import","method":"GET","ret":"obj","paytoqs":"ignore","url":"https://api.octopus.energy/v1/products/AGILE-22-08-31/electricity-tariffs/E-1R-AGILE-22-08-31-L/standard-unit-rates/?page_size=96","tls":"","persist":false,"proxy":"","insecureHTTPParser":false,"authType":"","senderr":false,"headers":[],"x":380,"y":220,"wires":[["22fdc0bfd7bdd329"]]},{"id":"65094b21c3938d73","type":"change","z":"1cc7d2e94a4815fe","g":"e42010beae8f7fa5","name":"Results","rules":[{"t":"set","p":"payload","pt":"msg","to":"payload.results","tot":"msg"},{"t":"set","p":"topic","pt":"msg","to":"export","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":540,"y":260,"wires":[["3c0e6412766b7286"]]},{"id":"22fdc0bfd7bdd329","type":"change","z":"1cc7d2e94a4815fe","g":"e42010beae8f7fa5","name":"Results","rules":[{"t":"set","p":"payload","pt":"msg","to":"payload.results","tot":"msg"},{"t":"set","p":"topic","pt":"msg","to":"import","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":540,"y":220,"wires":[["3c0e6412766b7286"]]},{"id":"3c0e6412766b7286","type":"join","z":"1cc7d2e94a4815fe","g":"e42010beae8f7fa5","name":"Combine","mode":"custom","build":"object","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"2","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"num","reduceFixup":"","x":700,"y":220,"wires":[["416e22ecfcbf5616"]]},{"id":"0ba1d9b378092267","type":"inject","z":"1cc7d2e94a4815fe","g":"e42010beae8f7fa5","name":"@ 16:05","props":[{"p":"payload"}],"repeat":"","crontab":"05 16 * * *","once":true,"onceDelay":"2","topic":"","payload":"","payloadType":"date","x":200,"y":220,"wires":[["db6ebccc4bb88b8d","63bba87f93e0eaae"]]},{"id":"416e22ecfcbf5616","type":"change","z":"1cc7d2e94a4815fe","g":"e42010beae8f7fa5","name":"Build & Save Tariff Array","rules":[{"t":"set","p":"payload","pt":"msg","to":"payload.import#$i.{\t \"from\": valid_from,\t \"upto\": valid_to,\t \"date\": $substring(valid_from,0,10),\t \"timefrom\": $substring(valid_from,11,5),\t \"timeupto\": $substring(valid_to,11,5),\t \"import\": value_inc_vat,\t \"export\": %.export[$i].value_inc_vat\t}^(from)","tot":"jsonata"},{"t":"set","p":"OctAgileTariff","pt":"flow","to":"payload","tot":"msg","dc":true}],"action":"","property":"","from":"","to":"","reg":false,"x":890,"y":220,"wires":[["56dd710454ea2e25"]]},{"id":"ce579e86db6fcc9b","type":"comment","z":"1cc7d2e94a4815fe","g":"e42010beae8f7fa5","name":"Octopus agile prices (2 days) JSONata V2.0.0","info":"","x":290,"y":180,"wires":[]},{"id":"be04e63b7258b035","type":"ha-sensor","z":"1cc7d2e94a4815fe","g":"e42010beae8f7fa5","name":"Octopus Agile Prices","entityConfig":"893ae24be6da2123","version":0,"state":"agile.now.from","stateType":"msg","attributes":[{"property":"array","value":"payload","valueType":"msg"},{"property":"import","value":"agile.now.import","valueType":"msg"},{"property":"export","value":"agile.now.export","valueType":"msg"},{"property":"import_next","value":"agile.next.import","valueType":"msg"},{"property":"export_next","value":"agile.next.export","valueType":"msg"}],"inputOverride":"allow","outputProperties":[],"x":920,"y":560,"wires":[[]],"server":""},{"id":"4430da6cfe290e39","type":"change","z":"1cc7d2e94a4815fe","g":"e42010beae8f7fa5","name":"Value Now","rules":[{"t":"set","p":"agile","pt":"msg","to":"(\t /* function to add <delta> minutes to now */\t $tstep:=function($mdelta) {\t $fromMillis($toMillis($now())+$mdelta*60000)\t };\t\t /* function to build key value from timestamp */\t /* strip fraction of seconds, floor minutes to 00 or 30, zero seconds */\t $keyis:=function($ts) {\t (\t $t:=$replace($ts, /(\\.[0-9]+)/, '');\t $m:=$substring($t,-6,2).$number()<30 ? '00': '30';\t $substring($t,0,14) & $m & \":00\" & $substring($t,-1);\t )\t };\t\t /* return object of 'now' and 'next' value objects */\t {\t \"now\": payload[from=$keyis($now())],\t \"next\": payload[from=$keyis($tstep(30))]\t };\t )","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":730,"y":560,"wires":[["be04e63b7258b035"]]},{"id":"3797b648d315c3a1","type":"change","z":"1cc7d2e94a4815fe","g":"e42010beae8f7fa5","name":"Read Array","rules":[{"t":"set","p":"payload","pt":"msg","to":"OctAgileTariff","tot":"flow","dc":true}],"action":"","property":"","from":"","to":"","reg":false,"x":490,"y":560,"wires":[["4430da6cfe290e39"]]},{"id":"bc4c506918ed4d9e","type":"change","z":"1cc7d2e94a4815fe","g":"e42010beae8f7fa5","name":"Import Low","rules":[{"t":"set","p":"topic","pt":"msg","to":"import","tot":"str"},{"t":"set","p":"sample","pt":"msg","to":"15","tot":"num"},{"t":"set","p":"payload","pt":"msg","to":"/* look at latest prices, sorted by ascending import cost */\t/* pick the lowest <sample size>, create new object for each */\t/* with {date, from, upto, cost} Sort by date & time */\t\tpayload[[48..95]]^(import)[[0..$$.sample-1]].{\t \"from\": from,\t \"upto\": upto,\t \"date\": date,\t \"timefrom\": timefrom,\t \"timeupto\": timeupto,\t \"value\": import\t}^(from)","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":390,"y":360,"wires":[["aa94392fea62febc"]]},{"id":"b98e955f8996efa2","type":"change","z":"1cc7d2e94a4815fe","g":"e42010beae8f7fa5","name":"Read Array","rules":[{"t":"set","p":"payload","pt":"msg","to":"OctAgileTariff","tot":"flow","dc":true}],"action":"","property":"","from":"","to":"","reg":false,"x":210,"y":360,"wires":[["bc4c506918ed4d9e","a28359905d33b762"]]},{"id":"c63c0e9f64999a1e","type":"inject","z":"1cc7d2e94a4815fe","g":"e42010beae8f7fa5","name":"Manual","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":210,"y":320,"wires":[["b98e955f8996efa2"]]},{"id":"a28359905d33b762","type":"change","z":"1cc7d2e94a4815fe","g":"e42010beae8f7fa5","name":"Export High","rules":[{"t":"set","p":"topic","pt":"msg","to":"export","tot":"str"},{"t":"set","p":"sample","pt":"msg","to":"15","tot":"num"},{"t":"set","p":"payload","pt":"msg","to":"/* for export values - as for import but */\t/* sorted from highest to lowest, top <n> */\t\tpayload[[48..95]]^(>export)[[0..$$.sample-1]].{\t \"from\": from,\t \"upto\": upto,\t \"date\": date,\t \"timefrom\": timefrom,\t \"timeupto\": timeupto,\t \"value\": export\t}^(from)","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":390,"y":400,"wires":[["aa94392fea62febc"]]},{"id":"59f0bbc0e3c76b0d","type":"change","z":"1cc7d2e94a4815fe","g":"e42010beae8f7fa5","name":"Groups","rules":[{"t":"set","p":"payload","pt":"msg","to":"/* get start and end indexes, and zip into a sequence array of [start, end] */\t/* map this array of sequences to an array of objects, one for each sequence to include */\t/* sequence number, index range, date, start time, stop time, count of half-hours */\t/* duration, and average (sum of values in sequence divided by count of items in sequence) */\t\t(\t $chain:=$zip(payload[position=\"start\"].index, payload[position=\"end\"].index);\t $map($chain, function($item, $index) {\t (\t $aa:=payload[$a:=$item[0]];\t $bb:=payload[$b:=$item[1]];\t $count:=$b-$a+1;\t {\t \"mode\": topic,\t \"sample\": sample,\t \"sequence\": $index+1,\t \"range\": ($a+1) & \"-\" & ($b+1),\t \"from\": $aa.from,\t \"upto\": $bb.upto,\t \"date\": $aa.date,\t \"timefrom\": $aa.timefrom,\t \"timeupto\": $bb.timeupto,\t \"periods\": $count,\t \"duration\": $count*30,\t \"average\": $round(($sum(payload[[$a..$b]].value)/$count)*1000)/1000\t })\t })^(from)[];\t)\t\t/* (>duration) gets longest periods first, however (from) sorts */\t/* chronologically, which is required for correct display on tariff graph */\t/* final [] places result into an array even if only one sequence group */\t","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":460,"y":460,"wires":[["8a65a4744d5f228a"]]},{"id":"aa94392fea62febc","type":"change","z":"1cc7d2e94a4815fe","g":"e42010beae8f7fa5","name":"Sequence V2","rules":[{"t":"set","p":"payload","pt":"msg","to":"/* For Version 2 - variable change needed */\t/* note index position, and test if items */\t/* are consecutive - forwards and backwards */\t/* set position as only, start, mid, end */\t\tpayload#$v.(\t $last:=$v<1 ? false : from=%.payload[$v-1].upto;\t $next:=upto=%.payload[$v+1].from;\t $position:=(\t $last ? ($next ? \"middle\" : \"end\") : ($next ? \"start\" : \"only\")\t );\t {\t \"from\": from,\t \"upto\": upto,\t \"date\": date,\t \"timefrom\": timefrom,\t \"timeupto\": timeupto,\t \"value\": value,\t \"index\": $v,\t \"link_last\": $last,\t \"link_next\": $next,\t \"position\": $position\t}\t)","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":560,"y":360,"wires":[["59f0bbc0e3c76b0d","224dc5d34ebe8967"]]},{"id":"87bddd29d5e5ab90","type":"link in","z":"1cc7d2e94a4815fe","g":"e42010beae8f7fa5","name":"Daily","links":["56dd710454ea2e25"],"x":245,"y":400,"wires":[["bc4c506918ed4d9e","a28359905d33b762"]]},{"id":"224dc5d34ebe8967","type":"join","z":"1cc7d2e94a4815fe","g":"e42010beae8f7fa5","name":"Join","mode":"custom","build":"object","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"2","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"num","reduceFixup":"","x":730,"y":360,"wires":[["3c01c3e92c0898b2"]]},{"id":"8a65a4744d5f228a","type":"join","z":"1cc7d2e94a4815fe","g":"e42010beae8f7fa5","name":"Join","mode":"custom","build":"object","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"2","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"num","reduceFixup":"","x":590,"y":460,"wires":[["a92b801c291d33d0"]]},{"id":"3c01c3e92c0898b2","type":"change","z":"1cc7d2e94a4815fe","g":"e42010beae8f7fa5","name":"Save Best","rules":[{"t":"set","p":"OctAgileBest","pt":"flow","to":"payload","tot":"msg","dc":true}],"action":"","property":"","from":"","to":"","reg":false,"x":870,"y":360,"wires":[[]]},{"id":"03b0c5c88010997c","type":"change","z":"1cc7d2e94a4815fe","g":"e42010beae8f7fa5","name":"Save Period","rules":[{"t":"set","p":"OctAgilePeriod","pt":"flow","to":"payload","tot":"msg","dc":true}],"action":"","property":"","from":"","to":"","reg":false,"x":870,"y":420,"wires":[[]]},{"id":"339092c8ec953466","type":"ha-sensor","z":"1cc7d2e94a4815fe","g":"e42010beae8f7fa5","name":"Octopus Agile Sequences","entityConfig":"6f8158bd34274dd3","version":0,"state":"$count(payload.export) + $count(payload.import)","stateType":"jsonata","attributes":[{"property":"export_array","value":"payload.export","valueType":"msg"},{"property":"import_array","value":"payload.import","valueType":"msg"},{"property":"both_array","value":"payload.both","valueType":"msg"},{"property":"bid_offer_spread","value":"$min(payload.import.average)-$max(payload.export.average)","valueType":"jsonata"}],"inputOverride":"allow","outputProperties":[],"x":910,"y":460,"wires":[[]],"server":""},{"id":"d8bb3057287698de","type":"link in","z":"1cc7d2e94a4815fe","g":"e42010beae8f7fa5","name":"Daily","links":["56dd710454ea2e25"],"x":615,"y":540,"wires":[["4430da6cfe290e39"]]},{"id":"a92b801c291d33d0","type":"change","z":"1cc7d2e94a4815fe","g":"e42010beae8f7fa5","name":"Table","rules":[{"t":"set","p":"payload.both","pt":"msg","to":"$append(payload.import, payload.export)\t","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":710,"y":460,"wires":[["03b0c5c88010997c","339092c8ec953466"]]},{"id":"56dd710454ea2e25","type":"link out","z":"1cc7d2e94a4815fe","g":"e42010beae8f7fa5","name":"Daily Update","mode":"link","links":["87bddd29d5e5ab90","d8bb3057287698de"],"x":1035,"y":220,"wires":[]},{"id":"ee45b426f60a08a0","type":"inject","z":"1cc7d2e94a4815fe","g":"e42010beae8f7fa5","name":"Half Hour","props":[{"p":"payload"}],"repeat":"","crontab":"*/30 0-23 * * *","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":210,"y":560,"wires":[["a9942de6076269cf"]]},{"id":"a9942de6076269cf","type":"delay","z":"1cc7d2e94a4815fe","g":"e42010beae8f7fa5","name":"10s","pauseType":"delay","timeout":"10","timeoutUnits":"seconds","rate":"1","nbRateUnits":"1","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":false,"allowrate":false,"outputs":1,"x":350,"y":560,"wires":[["3797b648d315c3a1"]]},{"id":"893ae24be6da2123","type":"ha-entity-config","server":"","deviceConfig":"","name":"SC Oct Agile Prices","version":"6","entityType":"sensor","haConfig":[{"property":"name","value":"Octopus Agile Prices"},{"property":"icon","value":""},{"property":"entity_picture","value":""},{"property":"entity_category","value":""},{"property":"device_class","value":""},{"property":"unit_of_measurement","value":""},{"property":"state_class","value":""}],"resend":true,"debugEnabled":false},{"id":"6f8158bd34274dd3","type":"ha-entity-config","server":"","deviceConfig":"","name":"SC Oct Agile Seq","version":"6","entityType":"sensor","haConfig":[{"property":"name","value":"Octopus Agile Sequence Table"},{"property":"icon","value":""},{"property":"entity_picture","value":""},{"property":"entity_category","value":""},{"property":"device_class","value":""},{"property":"unit_of_measurement","value":""},{"property":"state_class","value":""}],"resend":true,"debugEnabled":false}]
JSONata
This is an open-source language and execution engine (run using JavaScript) that operates on JSON data structures using a functional and declarative approach. JSONata has been added to Node-RED, and in the Node-RED HA add-on it has a few extra HA-specific functions to allow access to HA entities. (Thanks are due to the people who ported Node-RED into HA, and JSONata into Node-RED.)
Within Node-RED, JSONata can be found in several of the nodes, providing the opportunity to extract information from and/or perform predicate tests on a JSON data structure. The ‘change node’ has the ability to set a message variable to the result of a JSONata expression, using the ‘J:’ option, which provides a powerful opportunity to use JSONata to the full.
How JSONata works
JSONata executes a one-line statement, eg “payload.input.value_inc_vat”
Each part of the statement is parsed and evaluated as “<sequence> <operator> <expression>” by executing each operator found in the line, in turn, and from the left. Each operator is evaluated using the input sequence generated or taken from the left-hand side, the operator, the expression on the right-hand side, and the current context. The evaluation result is returned, and each result is then used as the input sequence to the next operator. At the end of the line, the final result is returned as the outcome of the entire line statement.
Each operator execution takes the current context, which begins as the top-level JSON document, and is then set to the outcome of each operation. Context will therefore change as the statement line is evaluated.
The basic execution in JSONata is carried out as a ‘JSON path search’, evaluating object keys found in the statement against the current context. Any key (such as ‘payload’ or ‘input’ or ‘value_inc_vat’) found in the current context is evaluated and the value of that key returned. In simple cases, this execution mirrors what we would expect from any JSON object path notation.
The main JSONata path operators are: -
‘.’ as a mapping function
‘’ as a filtering function
‘{}’ as a grouping function
‘^()’ as an ordering function (or sort)
Note that these operators are in addition to (overload) a traditional use as object path notation, as an array constructor and array address, and as a JSON object constructor.
Sequences: The execution of each operator evaluates, where appropriate (array or object), the left-hand side (from the current context) as a sequence of items, and applies the right-hand side as a function to each item from the sequence, in turn. The result will typically be another sequence, which is automatically returned in an array as the result.
(There are a few subtleties not yet mentioned, such as path searches can go down into nested structures to return all possible matches, ‘’ binds tighter than ‘.’, singleton items are still returned in an array, and the result sequence will be lifted and flattened for nested result objects.)
Testing and debugging JSONata
The people who created JSONata have provided a website for developing and testing code.
This comes with a few inbuilt examples, but it is very easy to paste our own JSON in the left-hand pane, write a JSONata expression in the top right pane, and immediately see the result in the bottom right pane.
TIP – if you want to copy a JSON object from Node-RED flow to paste into the try.jsonata website, set your debug node to ‘full message’ output and copy the full message object. This will then have payload (and topic etc) within the top-level document. When writing JSONata code, ‘payload’ at the front will be required. This means that your test code can then be pasted back into a Node-RED change node and it will work without modification.
There is an inbuilt JSON formatter (see top right of left-hand pane) which makes the pasted object easier to read. Editing the input JSON also has immediate effect, so it is very simple to test code by editing either/both the input JSON and the JSONata statement.
My JSONata code explained
Combine import and export tariffs into one array:
Note that any formatting here is cosmetic (done by using the formatter in the change node editor, and also by the formatter here - in JSONata # is an operator, not a comment). JSONata just sees one long line:
payload.import#$i.{
"from": valid_from,
"upto": valid_to,
"date": $substring(valid_from,0,10),
"time": $substring(valid_from,11,5),
"import": value_inc_vat,
"export": %.export[$i].value_inc_vat
}^(from)
try-jsonata link: https://try.jsonata.org/cUoa0QVAt
What this JSONata code does
I have exceeded the posting size limit, so if anyone is interested I can post my writeup explanation of the JSONata code separately.
Moving data to Home Assistant for display
Getting tariff prices for ‘now’ and ‘next period’
This part of the Node-RED flow runs every 30 minutes, a few seconds after the hour and half hour. Starting with the full tariff array (read back from context) I need to get the tariff price object where the ‘from’ timestamp is for now. Since the tariff time is always hh:00 or hh:30, I need to round the minutes and remove the seconds.
A Home Assistant sensor node is all that is required to move the data to HA. I have placed the current ‘timestamp’ value into the sensor state, and the full tariff array with the now/next import/export values into attributes. In HA these values can be displayed in an entities card using the attribute.
Table display
I use the custom component flex-table-card, which can display an array as a table. Here I display the best-price sequences. To more easily display both import and export sequences together on one card, I have combined both sets of data into one ‘both_array’ object first. As the flex-table-card needs to dig down into this object, the ‘modify:x.’ step is required for each object key.
type: custom:flex-table-card
title: Octopus Agile - best times
entities:
include: sensor.octopus_agile_sequence_table
columns:
- data: both_array
modify: x.mode
name: Mode
- data: both_array
modify: x.date
name: Date
- data: both_array
modify: x.timefrom
name: Start
- data: both_array
modify: x.timeupto
name: Stop
- data: both_array
modify: x.duration
name: Mins
- data: both_array
modify: x.average
name: Price (p)
Graphical display
I use the custom apexcharts-card to display the tariff prices. This requires some graph configuration settings as well as data generator code to get the display I want. I use the sensor octopus_agile_prices, which is my Node-RED sensor containing the tariff price array in the attribute variable ‘array’.
Card settings:
I set graph_span to 48h, and span to start at the beginning of the day, and offset by -1h so the graph runs from 23:00 yesterday to 23:00 tomorrow.
I set the tariff series as step-line, and use a data generator. This uses the attribute.array, and pulls the from (timestamp) and price (import/export) mapping from the full array to the new time/price array I need for the chart. The step-line will plot horizontally from the start (from) of the period data-point to the next data point. At the last data point I need to add one extra data point to make the last entry run horizontally on to the end of the day. Hence the last ‘final.push’ adding the ‘upto’ timestamp (of the last period) and the last period price.
On top of the tariff prices, I also plot the best-price sequences. This is a little more complex. The data comes from the other sensor, octopus_agile_sequence_table, and requires a data generator to build an array of all the start-end data points. As this is again a step-line plot, the first data point is at the start of the graph period with value zero, then the next data point is the first sequence-start time and sequence-start average (to give a horizontal from the day start to sequence start, then a vertical at the sequence start). My data plot array therefore starts with [start, 0], then builds [sequence-start, value] and [sequence-end, 0] to give the step-hump for each sequence. The end of the plot finishes at the end of the graph with [end, 0].
As a fudge, I actually plot the sequence lower value at -12, and then force the graph axis to display from -10, thus hiding the connecting horizontal lines. This works nicely for (most) negative import prices, but the settings can be adjusted to accommodate more dramatic ‘plunge pricing’ as required.
Anyway, it all seems to work!
type: custom:apexcharts-card
header:
show: true
title: Octopus Agile Prices
show:
last_updated: true
now:
show: true
label: now
graph_span: 48h
span:
start: day
offset: '-1h'
series:
- entity: sensor.octopus_agile_prices
data_generator: |
let prices = entity.attributes.array;
let ends = prices.length-1;
let final = prices.map((item, index) => {
return [item.from, item.import];
});
final.push([prices[ends].upto, prices[ends].import]);
return final;
curve: stepline
name: Import
show:
legend_value: false
extremas: true
stroke_width: 2
color: red
- entity: sensor.octopus_agile_prices
data_generator: |
let prices = entity.attributes.array;
let ends = prices.length-1;
let final = prices.map((item, index) => {
return [item.from, item.export];
});
final.push([prices[ends].upto, prices[ends].export]);
return final;
curve: stepline
name: Export
show:
legend_value: false
stroke_width: 2
color: green
- entity: sensor.octopus_agile_sequence_table
data_generator: |
let prices = entity.attributes.import_array;
let final = [];
let i=0;
let j=0;
final[j]=[start, -12];
j++;
for (i=0; i<prices.length; i++){
final[j] = [prices[i].from, prices[i].average];
final[j+1] = [prices[i].upto, -12];
j=j+2;
};
final[j]=[end, -12];
return final;
curve: stepline
name: Low Import
stroke_width: 1
color: purple
show:
legend_value: false
- entity: sensor.octopus_agile_sequence_table
data_generator: |
let prices = entity.attributes.export_array;
let final = [];
let i=0;
let j=0;
final[j]=[start, -12];
j++;
for (i=0; i<prices.length; i++){
final[j] = [prices[i].from, prices[i].average];
final[j+1] = [prices[i].upto, -12];
j=j+2;
};
final[j]=[end, -12];
return final;
curve: stepline
name: High Export
stroke_width: 1
color: blue
show:
legend_value: false
yaxis:
- min: -10
