# Extracting data from a TTN payload for insertion into MariadB
**URL:** https://discourse.nodered.org/t/extracting-data-from-a-ttn-payload-for-insertion-into-mariadb/51381
**Category:** General
**Created:** [22 September 2021 11:15 UTC](https://discourse.nodered.org/t/extracting-data-from-a-ttn-payload-for-insertion-into-mariadb/51381 "2021-09-22T11:15:27Z")
**Posts on this page:** 13
**Page:** 1
### Author:  [@moonlight](https://discourse.nodered.org/u/moonlight)
#### Post date: [22 September 2021 11:15 UTC](https://discourse.nodered.org/t/extracting-data-from-a-ttn-payload-for-insertion-into-mariadb/51381/1 "2021-09-22T11:15:27Z")
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:
```auto
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:
```auto
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.
```auto
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.
---
### Author:  [@jbudd](https://discourse.nodered.org/u/jbudd)
#### Post date: [22 September 2021 12:28 UTC](https://discourse.nodered.org/t/extracting-data-from-a-ttn-payload-for-insertion-into-mariadb/51381/2 "2021-09-22T12:28:08Z")
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
---
### Author:  [@zenofmud](https://discourse.nodered.org/u/zenofmud)
#### Post date: [22 September 2021 13:30 UTC](https://discourse.nodered.org/t/extracting-data-from-a-ttn-payload-for-insertion-into-mariadb/51381/3 "2021-09-22T13:30:12Z")
Do you have the `mqtt-in` node set to return a string, buffer, json object, base64?
---
### Author:  [@moonlight](https://discourse.nodered.org/u/moonlight)
#### Post date: [23 September 2021 07:32 UTC](https://discourse.nodered.org/t/extracting-data-from-a-ttn-payload-for-insertion-into-mariadb/51381/4 "2021-09-23T07:32:32Z")
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.
---
### Author:  [@moonlight](https://discourse.nodered.org/u/moonlight)
#### Post date: [23 September 2021 07:34 UTC](https://discourse.nodered.org/t/extracting-data-from-a-ttn-payload-for-insertion-into-mariadb/51381/5 "2021-09-23T07:34:36Z")
Thanks for your input. Yes mqtt node is set to autodetect.
---
### Author:  [@zenofmud](https://discourse.nodered.org/u/zenofmud)
#### Post date: [23 September 2021 07:48 UTC](https://discourse.nodered.org/t/extracting-data-from-a-ttn-payload-for-insertion-into-mariadb/51381/6 "2021-09-23T07:48:02Z")
Try changing it to a 'parsed JSON object' then you won’t need to use the `json` node
---
### Author:  [@moonlight](https://discourse.nodered.org/u/moonlight)
#### Post date: [23 September 2021 07:56 UTC](https://discourse.nodered.org/t/extracting-data-from-a-ttn-payload-for-insertion-into-mariadb/51381/7 "2021-09-23T07:56:10Z")
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.
---
### Author:  [@moonlight](https://discourse.nodered.org/u/moonlight)
#### Post date: [23 September 2021 08:27 UTC](https://discourse.nodered.org/t/extracting-data-from-a-ttn-payload-for-insertion-into-mariadb/51381/8 "2021-09-23T08:27:24Z")
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;`
---
### Author:  [@jbudd](https://discourse.nodered.org/u/jbudd)
#### Post date: [23 September 2021 10:29 UTC](https://discourse.nodered.org/t/extracting-data-from-a-ttn-payload-for-insertion-into-mariadb/51381/9 "2021-09-23T10:29:09Z")
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:
```auto
msg.topic = "INSERT INTO TTNsensors (barometrics) VALUES ( '"
+ msg.payload.uplink_message.decoded_payload.messages[0].measurementValue + "')"
```
---
### Author:  [@Colin](https://discourse.nodered.org/u/Colin)
#### Post date: [23 September 2021 10:49 UTC](https://discourse.nodered.org/t/extracting-data-from-a-ttn-payload-for-insertion-into-mariadb/51381/10 "2021-09-23T10:49:57Z")
> [@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.
---
### Author:  [@jbudd](https://discourse.nodered.org/u/jbudd)
#### Post date: [23 September 2021 10:55 UTC](https://discourse.nodered.org/t/extracting-data-from-a-ttn-payload-for-insertion-into-mariadb/51381/11 "2021-09-23T10:55:55Z")
Of course that's right. Thanks for the ticking off Colin 😃
---
### Author:  [@moonlight](https://discourse.nodered.org/u/moonlight)
#### Post date: [23 September 2021 12:08 UTC](https://discourse.nodered.org/t/extracting-data-from-a-ttn-payload-for-insertion-into-mariadb/51381/12 "2021-09-23T12:08:49Z")
Thanks, I'll give it a go. Learning lots of good stuff here today, remembering it for next time is the challenge.
---
### Author:  [@system](https://discourse.nodered.org/u/system)
#### Post date: [7 October 2021 12:08 UTC](https://discourse.nodered.org/t/extracting-data-from-a-ttn-payload-for-insertion-into-mariadb/51381/13 "2021-10-07T12:08:57Z")
This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.