MQTT input to MySQL

I just want to clarify, that i'm completely new in Node Red.
Hello, i have my project with NodeMCU+BME280+DS18B20 and tasmota fimware installed nad MQTT brocker.
So I have this input on my MQTT comming:

{"Time":"2020-09-20T16:57:06","DS18B20":{"Id":"3C01B5568855","Temperature":24.5},"BME280":{"Temperature":25.6,"Humidity":26.2,"DewPoint":4.8,"Pressure":942.5},"PressureUnit":"hPa","TempUnit":"C"}

and need to parse them, formatting and puting in MySQL.

Can I ask you to help me , how to read and have temperature from my DS18B20 sensor and Temperature and Humidity of my BME280 sensor and after that to format them in suitable format for MySQL.

Thank you in advance.

Hi, I have just set up my MQTT Sensor.

My flow recieves the data via MQTT and then (in my case) I convert it to a Javascript object before splitting off all of the messages using a function. Once the messages are broken up into their respective message parts, they are then each passed on to the Database, in this case InfluxDB.

The Debug Node is your friend here, you need to check all messages (not just msg.payload) and then see how they can be split up.

HTH

Colin

I did it! And now it is working.

1 Like
[{"id":"a1e9ebca.50c5d8","type":"tab","label":"dht2mysql","disabled":false,"info":""},{"id":"61361dea.7dc934","type":"mqtt in","z":"a1e9ebca.50c5d8","name":"DS18B20+BME280","topic":"tele/NodeMCU_Ds18b20_10BD3B/SENSOR","qos":"0","datatype":"auto","broker":"a839e467.aaa5f8","x":220,"y":300,"wires":[["179f71a4.be527e"]]},{"id":"37275d81.78e4f2","type":"debug","z":"a1e9ebca.50c5d8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":590,"y":240,"wires":[]},{"id":"179f71a4.be527e","type":"json","z":"a1e9ebca.50c5d8","name":"","property":"payload","action":"","pretty":false,"x":410,"y":300,"wires":[["37275d81.78e4f2","975e9b0d.3965d8"]]},{"id":"975e9b0d.3965d8","type":"function","z":"a1e9ebca.50c5d8","name":"","func":"var ds_temp = { payload: msg.payload.DS18B20.Temperature };\nvar bme_temp = { payload: msg.payload.BME280.Temperature };\nvar bme_humidity = { payload: msg.payload.BME280.Humidity };\nmsg.topic = \"INSERT into alibaba (time,location,temp,humidity) values (now(),'node-red','\" + msg.payload.BME280.Temperature +\"','\" + msg.payload.BME280.Humidity + \"');\"\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":670,"y":400,"wires":[["78a078ba.30f0a8","95f87265.ea439"]]},{"id":"78a078ba.30f0a8","type":"debug","z":"a1e9ebca.50c5d8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"topic","targetType":"msg","statusVal":"","statusType":"auto","x":870,"y":400,"wires":[]},{"id":"95f87265.ea439","type":"mysql","z":"a1e9ebca.50c5d8","mydb":"93023f05.61d9e","name":"","x":880,"y":340,"wires":[[]]},{"id":"a839e467.aaa5f8","type":"mqtt-broker","z":"","name":"","broker":"localhost","port":"1883","clientid":"","usetls":false,"compatmode":true,"keepalive":"60","cleansession":true,"birthTopic":"","birthQos":"0","birthPayload":"","closeTopic":"","closePayload":"","willTopic":"","willQos":"0","willPayload":""},{"id":"93023f05.61d9e","type":"MySQLdatabase","z":"","name":"","host":"192.168.1.101","port":"3306","db":"temp_izmer","tz":""}]
1 Like

Excellent!