[SOLVED] Jsonpath or JS transform for MQTT transform

Setup, Configuration and Use Beginners
1

Hi,

I am trying to transform incoming MQTT JSON. Working fine on other items, but not on this particular JSON.

Here is MQTT visible on broker:
[{“conso”: “6”, “time”: “11/07/2020”}, {“conso”: “2”, “time”: “12/07/2020”}, {“conso”: “0”, “time”: “13/07/2020”}, {“conso”: “1”, “time”: “14/07/2020”}, {“conso”: “4”, “time”: “15/07/2020”}, {“conso”: “3”, “time”: “16/07/2020”}, {“conso”: “5”, “time”: “17/07/2020”}, {“conso”: “4”, “time”: “18/07/2020”}, {“conso”: “2”, “time”: “19/07/2020”}, {“conso”: “4”, “time”: “20/07/2020”}, {“conso”: “3”, “time”: “21/07/2020”}, {“conso”: “3”, “time”: “22/07/2020”}]

Script running daily is producing this MQTT payload.

I would like to get “conso” value which is produced on daily basis into Openhab item.
Trying to get last value from JSON with JSONPATH.

Bonus would be to check that last value is indeed value from “yesterday”, ie day before the script is run. Because the MQTT payload is produced by extracting website data, and sometimes values may not yet be there. I suppose this would mean use a JS file though.

Any idea? Thanks

.things file:
Thing mqtt:topic:mosquitto:gazpar “Gazpar” (mqtt:broker:mosquitto) @ “Salon” {
Channels:
Type number : gazpar-consommation “Consommation Gaz” [ stateTopic=“gazpar”, transformationPattern=“JSONPATH:$…conso[(@.length-1)]” ]

.items file:
Number Gazpar_Consommation “Consommation gaz [%.d kWh]”
{ channel=“mqtt:topic:mosquitto:gazpar:gazpar-consommation” }

Error log:
2020-07-23 17:43:41.805 [WARN ] [t.generic.ChannelStateTransformation] - Executing the JSONPATH-transformation failed: An error occurred while transforming JSON expression.

2

Please use code fences. discourse tends to change some chars if not marked as code (for example

“” vs. ""

which leads to problems when looking into code.

In question of the jsonpath expression, I’m pretty sure this is not correct.

1 Like
3

I find it helpful to play with JSONPATH in rules to work out a solution.

var JSONdata = '[{"conso": "6", "time": "11/07/2020"}, {"conso": "2", "time": "12/07/2020"}, {"conso": "0", "time": "13/07/2020"}, {"conso": "1", "time": "14/07/2020"}, {"conso": "4", "time": "15/07/2020"}, {"conso": "3", "time": "16/07/2020"}, {"conso": "5", "time": "17/07/2020"}, {"conso": "4", "time": "18/07/2020"}, {"conso": "2", "time": "19/07/2020"}, {"conso": "4", "time": "20/07/2020"}, {"conso": "3", "time": "21/07/2020"}, {"conso": "3", "time": "22/07/2020"}]'
logInfo("test", "raw data " + JSONdata)
logInfo("test", "first element " + transform("JSONPATH", "$.[0]", JSONdata))
logInfo("test", "last element " + transform("JSONPATH", "$.[-1]", JSONdata))
logInfo("test", "last conso " + transform("JSONPATH", "$.[-1].conso", JSONdata))
logInfo("test", "last date " + transform("JSONPATH", "$.[-1].time", JSONdata))

results

2020-07-24 02:00:24.392 [INFO ] [.eclipse.smarthome.model.script.test] - raw data [{"conso": "6", "time": "11/07/2020"}, {"conso": "2", "time": "12/07/2020"}, {"conso": "0", "time": "13/07/2020"}, {"conso": "1", "time": "14/07/2020"}, {"conso": "4", "time": "15/07/2020"}, {"conso": "3", "time": "16/07/2020"}, {"conso": "5", "time": "17/07/2020"}, {"conso": "4", "time": "18/07/2020"}, {"conso": "2", "time": "19/07/2020"}, {"conso": "4", "time": "20/07/2020"}, {"conso": "3", "time": "21/07/2020"}, {"conso": "3", "time": "22/07/2020"}]
2020-07-24 02:00:24.394 [INFO ] [.eclipse.smarthome.model.script.test] - first element {conso=6, time=11/07/2020}
2020-07-24 02:00:24.396 [INFO ] [.eclipse.smarthome.model.script.test] - last element {conso=3, time=22/07/2020}
2020-07-24 02:00:24.397 [INFO ] [.eclipse.smarthome.model.script.test] - last conso 3
2020-07-24 02:00:24.400 [INFO ] [.eclipse.smarthome.model.script.test] - last date 22/07/2020

There is an assumption here that “last” is always “latest”.

If you really need to do any processing/comparisons on this, you will need to script it instead.
Maybe use javascript transform (but what will you return if the data does not meet expectations?)
Or extract conso and time to two Items and process in rule. That’s actually more difficult because you have two Item updates to synchronize.
Or extract the whole JSON element to a single String Item, then process that in a rule to confirm date and extract conso value.

3 Likes
4

nothing to add but…

Rossko57… not sure if if it gets mentioned enough, you are awesome!!!
thank you

5

Thanks a lot, I will try to test this in rules. I was not familiar with this way of testing yet.
I am extracting the daily gas consumption from a website, sometimes may not work well for many reasons.
I would like to be able to catch value if it corresponds to current day when data is published over MQTT. By catching latest value, and putting it into an item, I am assuming I should be able to get things properly displayed in influxdb/grafana.

Will work with this over the weekend and mark solution when it will work.

6

If you need to validate this data before acting on it, I would think your best bet is to -
extract the whole [last] JSON element to a single String Item, then process that in a rule to confirm date and extract conso value.
If the date is wrong etc. don’t update your ‘real’ item.

7

Hi @S1eepy - may I ask how you retrieve your Gazpar consumption and send it over MQTT, is it a publicly available script? (I’m asking because mine was just installed yesterday…)

8

Hello, yes look for domoticz gazpar => https://github.com/empierre/domoticz_gaspar
shell script I run using Cron. I have added the mosquitto commands (I’m not an expert on github…)
I will start a new thread for this, maybe we can improve, sometimes I am getting bogus values…

Added new thread here: Gazpar data retrieval example

1 Like