MQTT data to MySQL over Node-RED

[Adam S]

I am trying to take data from The Things Network over MQTT to a MySQL DB. I posted the question here in with one of my issues: https://stackoverflow.com/questions/39310266/node-red-json-parsing-not-working. 

I want to take this JSON data: 

{"payload":"Gc4FaAAAFwBoAA==","port":223,"counter":4527,"dev_eui":"00DEAD00BEEF0002","metadata": [{"frequency":902.3,"datarate":"SF10BW125","codingrate":"4/5","gateway_timestamp":1299905004,"channel":0,"server_time":"2016-09- 03T18:40:46.106713105Z","rssi":-31,"lsnr":12.3,"rfchain":0,"crc":1,"modulation": "LORA","gateway_eui":"1234567887654321","altitude":0,"longitude":0,"latitude":0} ]}

Below is what I am looking to insert into the DB.

- The first 3 characters of the payload

- The "rssi" and "latitude" and "longitude"

Can someone guide me though this?

[Julian Knight]

The slice function will extract a string, you could also use substring.

To get the rssi value, msg.metadata.rssi

Similarly for the others.

[Mark Setrem]

Julian missed the fact that msg.metadata is an array

so the rss value is actually var msg.metadata[0].rssi;

Its worth reading up on javascript objects and arrays.  Easiest way is probably to attach a function node to the MQTT in and then a debug node to the output of the function node. 

Within the function node create different properties of the msg object for the bits you are interested in 

e.g. var msg.rssi=msg.metadata[0].rssi;

 You can then configure the debug to output the property of msg ( e.g. msg.metadata[0].rssi;) until you the bit you want.

When you have collected all the bits you need you will need to configure the message to send to the sql node you are using which will depend on which node and the configuration of the database you are using.

[Julian Knight]

Oops! Sorry. You are, as always, correct Mark :)

Adam, I have to say that, while learning JS, this was what caught me out the most as well. It can be really hard to get your head around the complexities of JavaScript objects. It does - eventually - get a bit easier!