Extracting data from a TTN payload for insertion into MariadB

[moonlight]

Gooday, I have a SenseCAP LoRa barometer connected to The Things Network and trying to extract the payload delivered by a MQTT node. The payload(one big object) is as follows:

22/09/2021, 19:29:38[node: c820ff3f.fc1bf](http://192.168.1.160:1880/#)v3/barosense01@ttn/devices/eui-2cf7f127325000de/up : msg.payload : string[1783]

"{"end_device_ids":{"device_id":"eui-2cf7f127325000de","application_ids":{"application_id":"barosense01"},"dev_eui":"2CF7F127325000DE","join_eui":"8000000000000009","dev_addr":"260DBDE5"},"correlation_ids":["as:up:01FG6DDK92YFRZ9J62YMXPWA66","gs:conn:01FG16B14S42TWEKV9YYR111RC","gs:up:host:01FG16B2KR9NTJ1MGNC387YA6A","gs:uplink:01FG6DDK1CG1EJ7KFXM36YAPMF","ns:uplink:01FG6DDK1EV1ACVHG8Q4TYTBJ1","rpc:/ttn.lorawan.v3.GsNs/HandleUplink:01FG6DDK1E8WQNYYGGV4SB0GMK","rpc:/ttn.lorawan.v3.NsAs/HandleUplink:01FG6DDK925CQ8F2VJPRH2AGCK"],"received_at":"2021-09-22T09:29:36.291418585Z","uplink_message":{"session_key_id":"AXvxx05t+WEkWnl/zQvOEg==","f_port":2,"f_cnt":272,"frm_payload":"AQUQ6BIYBgpg","decoded_payload":{"err":0,"messages":[{"measurementId":4101,"measurementValue":102241,"type":"report_telemetry"}],"payload":"010510E81218060A60","valid":true},"rx_metadata":[{"gateway_ids":{"gateway_id":"gladesville-gateway1","eui":"A84041FFFF1EDFB8"},"time":"2021-09-22T09:29:36.044922Z","timestamp":121195..."

The part I wish to extract and insert into MariadB is the 102241 part of "measurementValue":102241. I have a function node which contains the following:

msg.baro = msg.payload.measurementValue;
msg.topic = "INSERT INTO TTNsensors(barometrics) VALUES('"+msg.baro + "');"
return msg;

Following the mysql node, I get following debug message and nothing is inserted into the database.

22/09/2021, 19:29:40[node: e347f173.4d1de](http://192.168.1.160:1880/#)INSERT INTO TTNsensors(barometrics) VALUES('undefined'); : msg : Object
object
topic: "INSERT INTO TTNsensors(barometrics) VALUES('undefined');"
payload: object
qos: 0
retain: false
_msgid: "ef678dbd.24a44"
baro: undefined

I am guessing the problem lies in the payload extraction function node. Can I get some guidance in what I need to change to make this work.
Cheers.

[jbudd]

It's hard to be sure since you have not supplied the entire message payload

Is your msg.payload a javascript object or is it a string?
If it's a string but contains valid JSON you can run it through a Json node to create an object.

Once it is in the right format I think measurementValue will be msg.payload.uplink_message.messages.measurementValue

Here is an example of debug output.
You see that msg.payload is an Object and you can explore it's properties with the little grey triangles.
The temperature reading is at msg.payload.Readings.temperature

[zenofmud]

Do you have the mqtt-in node set to return a string, buffer, json object, base64?

[moonlight]

I think it must be a string as in the first line of the payload it says: msg.payload : string[1783].In my initial post I copied the whole message as output in a debug node after the mqtt node.

I have a couple of other sensors which present the data as an object(like in your temp, hum, illum. example) and I have no problems extracting the values and insertion into MariadB.

So the solution is to run it through a Json parser node.? Will give it a try and see if it looks OK.

[moonlight]

Thanks for your input. Yes mqtt node is set to autodetect.

[zenofmud]

Try changing it to a 'parsed JSON object' then you won’t need to use the json node

[moonlight]

Done will see what happens. Node sends data every 30 mins.
Don't know whether this confuses things but I read a post of yours assisting another bloke and to use a split - switch - split series of nodes and was able to extract just the barometric value eg 102241 Not sure of the next step for the database insert though.

[moonlight]

Looks good.

Now the copied path is: payload.uplink_message.decoded_payload.messages[0].measurementValue

Don't know where the [0] came from but will see if it works.

You beauty it works. Thanks guys. Here is my decode for completeness.

msg.baro = msg.payload.uplink_message.decoded_payload.messages[0].measurementValue; msg.topic = "INSERT INTO TTNsensors(barometrics) VALUES('"+msg.baro + "');" return msg;

[jbudd]

I keep forgetting the Mqtt In node's option to output as JSON. Shame the Exec node doesn't have the same options.

FYI you could avoid creating the new message property msg.baro like this:

msg.topic = "INSERT INTO TTNsensors (barometrics) VALUES ( '" 
+  msg.payload.uplink_message.decoded_payload.messages[0].measurementValue + "')"

[Colin]

@jbudd

I keep forgetting the Mqtt In node's option to output as JSON

To be pedantic, the option is to not output as JSON, but convert the JSON it gets in to a javascript object. So outputing "a parsed JSON object" means it parses the JSON and outputs an Object.

[jbudd]

Of course that's right. Thanks for the ticking off Colin

[moonlight]

Thanks, I'll give it a go. Learning lots of good stuff here today, remembering it for next time is the challenge.