Jsonata test works but doesn't in the flow

Hi, its taken me a long while to debug a problem where I want to capture all the sensor info I care about from a bunch of sensors (water, temp/humidity, motion, remotes) via Zigbee2Tasmota and I've got quite stuck where my jsonata code works perfectly in the test panel, but when applied to real incoming messages it does not. I've hit a brick wall and could do with some help. This jsonata takes some getting used to! I wish there was a really full nodered/jsonata tutorial or cookbook - it would really help. Browsing the forums has been really helpful so far, but I'm needing some specific help with getting the Temperature extracted.

Typical payload from z2t

{
    "ZbReceived": {
        "0x9758": {
            "Device": "0x9758",
            "Name": "WiserRoomThermostat1",
            "FE03/0020": "ENV,-32768,1908,5791",
            "LastMessageLQI": 120,
            "LastMessageRSSI": -70,
            "0B05/E001": 22434,
            "Endpoint": 1,
            "LinkQuality": 55
        }
    }
}

My jsonata

{    
   "linkQuality": **.LinkQuality,
   "waterLeak": **.Water,
   "power": **.Power,
   "temperature": **.Temperature,
   "utility": **.Utility,
   "temperatureRoom": $number($split($lookup(ZbReceived.*,$keys(ZbReceived.*)[2]),',')[2])/100
}

Result from the "Test" panel in the node

{
    "linkQuality": 55,
    "temperatureRoom": 19.08
}

Result from streaming messages into debug

topic: "sensors/climate/WiserRoomThermostat1"
payload: {
  linkQuality: 45
  }
qos: 0
retain: false
_msgid: "1eb5320f.ba33de"

Any solutions, pointers or tips to making this better would be much appreciated. Currently the flow switches according to the sensor type (parsing the name) which pipes into topic creators, then to this payload node.

Test data...

JSONata...

Real data...

Maybe because your test data & JSONata has a capital L, the real data has a lower case l?

It's not that Steve as the **.Linkquality matches as expected, I'm just setting that to new keys, camelcased. The trouble is the temperature jsonata is not matching.

Ah ok. So are you are trying to split the value in FE03/0020 by a , and divide the last element by 100?

Why not just do it in JavaScript (function node)?

Ps, can you capture and post a real sample message that doesn't work? I suspect the order of properties are different in you incoming data & as you are using $keys[2] it may not be the property you are actually wanting.

   $lookup(ZbReceived.*,$keys(ZbReceived.*)[2]),',')[2])

Try payload.ZbReceived.*

Yep, I'm trying to get that temperature value but that string `FE03/0020' isn't static amongst all the sensors, but I could regex it. You're right about the ordering, I'd considered this but my jsonata isn't good enough to find the value matching ENV via search rather than ordering.

I'd prefer jsonata as I'm trying to master it, unless it wasn't designed for this kind of extracting. But I will try it in JS.

That was the real sample message in debug and this is what I see in MQTT.

Didn't work unfortunately. In the test panel the temp match disappears and node-red doesn't like it when the payload is passed into the node with this error message.
Screenshot from 2021-01-05 00-28-22

Thanks for the help guys.

Hi,

Its failing with that error because your jsonata received a payload that doesnt include a value that can be $split ? It worked fine if Water or Power were missing but $split didnt like the absent value :wink:

"FE03/0020": "ENV,-32768,1908,5791",

What if you use for temperatureRoom

 {    
   "linkQuality": **.LinkQuality,
   "waterLeak": **.Water,
   "power": **.Power,
   "temperature": **.Temperature,
   "utility": **.Utility,
   "temperatureRoom": $number($split(**.`FE03/0020`, ',')[2])/100
}

The thing is the key "FE03/0020" changes for the different sensors and I think possibly even on the one sensor. Ideally there'd be a regex for this, which I'm looking at the jsonata docs for info.

maybe possibly storing a boolean variable $temp whether the msg has a "ENV" so that means it a temperature. and after using that as a condition (?) to do the $split ?

(
 $temp := $boolean(*.*.$sift(function($v, $k) {$v ~> /^ENV/})); 

 {    
   "linkQuality": **.LinkQuality,
   "waterLeak": **.Water,
   "power": **.Power,
   "temperature": **.Temperature,
   "utility": **.Utility,
   "temperatureRoom": $temp ? $number($split($lookup(ZbReceived.*, $keys(ZbReceived.*)[2]),',')[2])/100
}

)

but as Steve said its better to do it with JS as it is risky with the property order

Is the object ZbReceived in the payload property or the msg property?

Tried that code UnborN but unfortunately it didn't pickup the temperature. I'm struggling with the JS too, but I'd like to think jsonata can do this, searching through the value strings, to find one matching ENV and presenting that containing string, for manipulation.

It's in the payload property/object and is, afaik, a default from zigbee2tasmota

{
   "ZbReceived": {
       "0x9758": {
           "Device": "0x9758",
           "Name": "WiserRoomThermostat1",
           "FE03/0020": "ENV,-32768,1908,5791",
           "LastMessageLQI": 120,
           "LastMessageRSSI": -70,
           "0B05/E001": 22434,
           "Endpoint": 1,
           "LinkQuality": 55         
       }     
   } 
}

Indeed it needed some changes in the code since the data is in payload.

Test flow:

[{"id":"f48ebb5a.01a278","type":"inject","z":"1c06008f.add8e7","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"{\"ZbReceived\":{\"0x9758\":{\"Device\":\"0x9758\",\"Name\":\"WiserRoomThermostat1\",\"FE03/0020\":\"ENV,-32768,1908,5791\",\"LastMessageLQI\":120,\"LastMessageRSSI\":-70,\"0B05/E001\":22434,\"Endpoint\":1,\"LinkQuality\":55}}}","payloadType":"json","x":420,"y":380,"wires":[["76371ad5.eb3dcc"]]},{"id":"76371ad5.eb3dcc","type":"change","z":"1c06008f.add8e7","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"(\t $temp := $boolean(payload.*.*.$sift(function($v, $k) {$v ~> /^ENV/})); \t\t {    \t   \"linkQuality\": **.LinkQuality,\t   \"waterLeak\": **.Water,\t   \"power\": **.Power,\t   \"temperature\": **.Temperature,\t   \"utility\": **.Utility,\t   \"temperatureRoom\": $temp ? $number($split($lookup(payload.ZbReceived.*, $keys(payload.ZbReceived.*)[2]),',')[2])/100\t}\t\t)","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":610,"y":380,"wires":[["4f4e541b.9bf71c"]]},{"id":"4f4e541b.9bf71c","type":"debug","z":"1c06008f.add8e7","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":840,"y":380,"wires":[]}]

Expression:

(
 $temp := $boolean(payload.*.*.$sift(function($v, $k) {$v ~> /^ENV/})); 

 {    
   "linkQuality": **.LinkQuality,
   "waterLeak": **.Water,
   "power": **.Power,
   "temperature": **.Temperature,
   "utility": **.Utility,
   "temperatureRoom": $temp ? $number($split($lookup(payload.ZbReceived.*, $keys(payload.ZbReceived.*)[2]),',')[2])/100
}

)
1 Like

payload.ZbReceived.* is correct please show how you tried to use this as it should work.

1 Like

It works! Your idea with the $temp function/expression works very well, I'll use that again. Thanks very much.

Screenshot from 2021-01-05 21-09-31

You're right, that worked too, thanks! I hadn't realised I'd needed to add payload explicitly. I'm mindful not to rely on positional checks though, so the additional $temp method from @UnborN is the way to go.

Screenshot from 2021-01-05 21-06-48

Flow

As can be seen in both solutions I get lots of errors from msg.payloads that don't parse well. I take it, even though it may not be damaging, its good form in node-red to eradicate errors and have the msg flow dropped cleanly with logging maybe?

Here's the full flow with both solutions. I need to add on the import of all the measurements found to InfluxDB which is the pimary task. I'll loop through the key/values per topic/payload.

[{"id":"3823f210.07d7ae","type":"mqtt in","z":"571fee50.8f2b1","name":"","topic":"tele/sonoff/zigbee/bridge/SENSOR","qos":"2","datatype":"json","broker":"2a8957f2.2dd3b8","x":160,"y":720,"wires":[["92322423.951718"]]},{"id":"676e5ddc.c6a8c4","type":"debug","z":"571fee50.8f2b1","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":730,"y":860,"wires":[]},{"id":"92322423.951718","type":"switch","z":"571fee50.8f2b1","name":"Sensors","property":"**.Name","propertyType":"jsonata","rules":[{"t":"cont","v":"WaterSensor","vt":"str"},{"t":"cont","v":"SmartPlug","vt":"str"},{"t":"cont","v":"RemoteControl","vt":"str"},{"t":"cont","v":"TempHumidity","vt":"str"},{"t":"cont","v":"RoomThermostat","vt":"str"},{"t":"cont","v":"MotionSensor","vt":"str"}],"checkall":"false","repair":false,"outputs":6,"x":120,"y":820,"wires":[["c5046a2a.b71598"],["94f839b7.2c44d8"],["228c2569.498bfa"],["b56a2206.87616"],["b56a2206.87616"],["578f94f2.24f0dc"]]},{"id":"c5046a2a.b71598","type":"change","z":"571fee50.8f2b1","name":"water","rules":[{"t":"set","p":"topic","pt":"msg","to":"$join([\"sensors/water/\",payload.ZbReceived.*.Name])","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":270,"y":780,"wires":[["86911f5e.86a28","5793b1c4.1a139"]]},{"id":"228c2569.498bfa","type":"change","z":"571fee50.8f2b1","name":"controls","rules":[{"t":"set","p":"topic","pt":"msg","to":"$join([\"sensors/controls/\",payload.ZbReceived.*.Name])","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":280,"y":860,"wires":[["86911f5e.86a28","5793b1c4.1a139"]]},{"id":"94f839b7.2c44d8","type":"change","z":"571fee50.8f2b1","name":"sockets","rules":[{"t":"set","p":"topic","pt":"msg","to":"$join([\"sensors/sockets/\",payload.ZbReceived.*.Name])","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":280,"y":820,"wires":[["86911f5e.86a28","5793b1c4.1a139"]]},{"id":"b56a2206.87616","type":"change","z":"571fee50.8f2b1","name":"climate","rules":[{"t":"set","p":"topic","pt":"msg","to":"$join([\"sensors/climate/\",payload.ZbReceived.*.Name])","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":280,"y":900,"wires":[["86911f5e.86a28","5793b1c4.1a139"]]},{"id":"578f94f2.24f0dc","type":"change","z":"571fee50.8f2b1","name":"motion","rules":[{"t":"set","p":"topic","pt":"msg","to":"$join([\"sensors/motion/\",payload.ZbReceived.*.Name])","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":270,"y":940,"wires":[["86911f5e.86a28"]]},{"id":"5793b1c4.1a139","type":"change","z":"571fee50.8f2b1","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"{    \t    \"v\":1,\t    \"linkQuality\": **.LinkQuality,\t   \"waterLeak\": **.Water,\t   \"power\": **.Power,\t   \"temperature\": **.Temperature,\t   \"utility\": **.Utility,\t   \"temperatureRoom\": $number($split($lookup(payload.ZbReceived.*,$keys(payload.ZbReceived.*)[2]),',')[2])/100\t}","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":520,"y":860,"wires":[["676e5ddc.c6a8c4"]]},{"id":"86911f5e.86a28","type":"change","z":"571fee50.8f2b1","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"(\t $temp := $boolean(payload.*.*.$sift(function($v, $k) {$v ~> /^ENV/})); \t\t {    \t   \"v\":2,\t   \"linkQuality\": **.LinkQuality,\t   \"waterLeak\": **.Water,\t   \"power\": **.Power,\t   \"temperature\": **.Temperature,\t   \"utility\": **.Utility,\t   \"temperatureRoom\": $temp ? $number($split($lookup(payload.ZbReceived.*, $keys(payload.ZbReceived.*)[2]),',')[2])/100\t}\t\t)","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":520,"y":920,"wires":[["7007a64b.538f98"]]},{"id":"7007a64b.538f98","type":"debug","z":"571fee50.8f2b1","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":730,"y":920,"wires":[]},{"id":"2a8957f2.2dd3b8","type":"mqtt-broker","name":"XXX","broker":"YYY","port":"1883","clientid":"","usetls":false,"compatmode":false,"keepalive":"60","cleansession":true,"birthTopic":"","birthQos":"0","birthPayload":"","closeTopic":"","closeQos":"0","closePayload":"","willTopic":"","willQos":"0","willPayload":""}]

This JSONata expression may be better

{        "linkQuality": **.LinkQuality,    "waterLeak": **.Water,    "power": **.Power,    "temperature": **.Temperature,    "utility": **.Utility,    "temperatureRoom": 
   
   $each(payload.ZbReceived.*, function($v){$contains($string($v), /^ENV/) ? $number($split($v,",")[2])/100})
}
2 Likes

good job @E1cid that looks a lot cleaner ..
and also your solution doesn't depend on getting the key[2] property that was making the code a bit unreliable (if im reading it correctly)

Excellent. Its a better solution for me too as I realised that the Humidity was embedded in the string as well, so this jsonata easily modifiable, could pick up that variable too.

Example message

{
    "ZbReceived": {
        "WiserRoomThermostat1": {
            "Device": "0x9758",
            "Name": "WiserRoomThermostat1",
            "FE03/0020": "ENV,-32768,1791,6010",
            "LastMessageLQI": 0,
            "LastMessageRSSI": 0,
            "0B05/E001": 0,
            "Endpoint": 1,
            "LinkQuality": 162
        }
    }
}

jsonata

{   "linkQuality": **.LinkQuality,  
    "waterLeak": **.Water,
    "power": **.Power,
    "dimmerUp": **.DimmerUp,
    "dimmerDown": **.DimmerDown,
    "dimmerMove": **.DimmerMove,
    "dimmerStepDown": **.DimmerStepDown,
    "dimmerStop": **.DimmerStop,
    "arrowClick": **.ArrowClick,
    "arrowHold": **.ArrowHold,
    "arrowRelease": **.ArrowRelease,
    "temperature": **.Temperature,
    "humidity": **.Humidity,
    "temperatureRoom": $each($.ZbReceived.*, function($v){$contains($string($v), /^ENV/) ? $number($split($v,",")[2])/100}),
    "humidityRoom": $each($.ZbReceived.*, function($v){$contains($string($v), /^ENV/) ? $number($split($v,",")[3])/100})
 }

Result

{
    "linkQuality": 162,
    "temperatureRoom": 17.91,
    "humidity": 60.1
}

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.