Extract data from an MQTT payload

Hi all,

I am a newbie and tried to connect with an MQTT broker to the TTN network and to extract data from my nodes in the TTN network. In the next step I want to put this data into an influx db. There are two sensors on this board which measures temperature, pressure and humidity. One is on the top (top) and the other on the bottom (bot). How can I extract these two pairs (temp, pressure, humidity) from both sensors ?
Here is my msg object:

"{"app_id":"isyboard",
"dev_id":"isyboard-2106",
"hardware_serial":"0004A30B002821A6",
"port":98,
"counter":17773,
"payload_raw":"DhYAALREJh8BALNAJiQ=",
"payload_fields":{"bat":3.61,
"bot":{"humi":32,"pres":976.4,"temp":17.9},

  • "top":{"humi":32,"pres":976.4,"temp":17.9}},*
    "metadata":{"time":"2019-03-10T13:11:03.407677436Z","frequency":867.7,"modulation":"LORA","data_rate":"SF7BW125","airtime":66816000,"coding_rate":"4/5","gateways":[{"gtw_id":"eui-b827ebfffe86efb6","timestamp":2114939276,"time":"2019-03-10T13:11:03.388643Z","channel":6,"rssi":-59,"snr":9,"rf_chain":0,"latitude":50.073685,"longitude":14.416852,"altitude":15,"location_source":"registry"},{"gtw_id":"eui-b827ebfffe4d6081","timestamp":2123351012,"time":"2019-03-10T13:11:03.389984Z","channel":6,"rssi":-100,"snr":9,"rf_chain":0,"latitude":50.07374,"longitude":14.416893,"altitude":15,"location_source":"registry"}]}}"

Many thanks in advance !
Peter

If it has come from MQTT it's most likely to be a String type - and you really need it as JSON so pass it through a JSON node to format it up - then read this https://nodered.org/docs/user-guide/messages which should help you understand how to extract the pieces you want from that object (probably) using a change node.

Don't hesitate to come back when you get stuck.

I will try !
Thanks so far for your help.
Br Peter

Hi
I have a similar problem but can't figure it out trough the link.
I want to extract this dataUnbenannt

Hi @Tobitobs,

Have you followed the advice @dceejay provided ?

Your payload is currently a JSON string. You need to parse it to an object before you can access is contents. The mqtt node provides an option to do that for you, or you can use a JSON node.

thanks it worked. The JSON node was the one missing thing I needed

Hello,

quite old thread but I am facing with same problem. I have no idea how to get out values, from the output of MQTT node. It seems it is a string:

pms-hpma115s0/devices/hpma115s0/up : msg.payload : string[757]

Output of MQTT node is configured as "auto-detect" - although none of other options helps. Output is indeed valid JSON file. If i try to be more precise: point is to get

image

instead of

image

Thank you for any suggestion!

Show us what the string looks like. You showed a short string and a buffer but the string isn't JSON and obviously isn't 757 long.

If it is supposed to be valid json what do you get it you say you want Parsed json?

Sorry, I couldn't attaced third picture. Here is whole string:

{"app_id":"pms-hpma115s0","dev_id":"hpma115s0","hardware_serial":"00AFFA6F66E318AB","port":1,"counter":555,"payload_raw":"TAEJuXQO9gNfAMsBFAAVACwCAAA=","payload_fields":{"CRC_Er_count":0,"humidity":37,"mA":95,"mW":459,"m_counter":556,"pm10":21,"pm25":20,"pressure":1014,"temperature":24.89,"voltage":3.32},"metadata":{"time":"2020-04-19T20:25:25.80145338Z","frequency":868.5,"modulation":"LORA","data_rate":"SF7BW125","airtime":71936000,"coding_rate":"4/5","gateways":[{"gtw_id":"gw_rence_1","gtw_trusted":true,"timestamp":736873955,"time":"2020-04-19T20:25:25Z","channel":2,"rssi":-66,"snr":7,"rf_chain":1,"latitude":45.889812,"longitude":13.669458,"altitude":60}],"latitude":45.88979,"longitude":13.669476,"altitude":55,"location_source":"registry"}}

And what happens if you select Parsed JSON as the output in mqtt node?

This is parsed JSON object from MQTT node:

Maybe i have to explain what is behind this problem. I believe this will be useful for someone else due to changes in way of capturing data from TTN. As we know there is no more support for "ttn message" node in last version of NodeRed. Instead we have to use NodeRed's standard MQTT client node to access data. So far so good, but this lead to different way of decoding TTN message. I mean, TTN decoder is out of function and we have to decode useful data from payload before we send them into database (influx, in my case).
This could be done - by my knowledge - with function node in NodeRed. But please let me know if there is any other better option.

Data on node are coded on this way: https://github.com/thesolarnomad/lora-serialization

OK, with the MQTT node correctly configured all the data are there and accessible. Do you need further help?
If you are not sure how to extract the data then have a read of this.
https://nodered.org/docs/user-guide/messages

Yes, the problem still persists. If i connect to influx node, i receive this error:

image

Look at the Info tab for the influx node where it tells you how the data must be formatted. Is that how yours is?

This is what i don't understand:

image

Influx accept format on the left (payload_raw: buffer[20]), but not one (payload_raw:long string) on the right.

I find that surprising. What exactly are you doing with that data? You say you are sending it to an Influxdb, but that format it not what the standard Influxdb Out node expects.

I want to get that data in Grafana.I still have an old version of NodeRed with TTN node and packet, which goes to influx is:

You still haven't said what node you are sending the data to. node-red-contrib-something probably.

In an old version of NodeRed i have:

But in new one i would like to achieve this:

image

I hope i answered

What type of node is PMS DB? An Influx Out or an Influx Batch. I guess it is the Influx Out. In which case you have to look at the info tab to see how to format the data.