MQTT to MYSQL help is needed

General
, ,
1

Hi Everyone,

I am new to Node-Red but have been running some home automation via HA for a while.
With a new project I have a electricity monitor that pass's out 6 messages via MQTT and I would like to write these into a MYSQL database but I am not getting it right.

below are examples of each message from the MQTT device that I have configured in Node-Red

{"id":"3701211100108","ua":231.59,"ub":0,"uc":0,"ia":0,"ib":0,"time":"20220322202100","isend":"0"}
{"id":"3701211100108","ic":0.04,"uab":232.028,"ubc":0,"uca":232.028,"pa":0,"time":"20220322202100","isend":"0"}
{"id":"3701211100108","pb":0,"pc":0,"zyggl":0.0009,"qa":0.0073,"qb":0,"time":"20220322202100","isend":"0"}
{"id":"3701211100108","qc":0,"zwggl":0.002,"sa":0.0035,"sb":0,"sc":0,"time":"20220322202100","isend":"0"}
{"id":"3701211100108","zszgl":0.0049,"pfa":0.621,"pfb":0,"pfc":0,"zglys":0.621,"f":49.93,"time":"20220322202100","isend":"0"}
{"id":"3701211100108","unb":0.999,"inb":0,"pdm":-0.0004,"qdm":0.0003,"sdm":0.0015,"ig":2039.708,"time":"20220322202100","isend":"1"}

I have my MQTT node Feeding a JSON node with the debug node to get the above.

What I need is for these 6 messages that all have the same time stamp to be written into one line in the table in the MYSQL database.

Any assistance on this would be amazing.

Thanks.

2

Messages arrive one at a time. To put all values into a database in one row you will need ALL values in one message.

So do that bit first...

See this article in the cookbook for an example of how to join messages into one object.

3

thanks will give it a read.

4

I see that isend on last message is "1", if this signifies the last message then you could use this to merge all objects and complete join when isend equals "1"
e.g.

[{"id":"2c3a94cf.7d5cec","type":"inject","z":"bf9e1e33.030598","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"id\":\"3701211100108\",\"ua\":231.59,\"ub\":0,\"uc\":0,\"ia\":0,\"ib\":0,\"time\":\"20220322202100\",\"isend\":\"0\"},{\"id\":\"3701211100108\",\"ic\":0.04,\"uab\":232.028,\"ubc\":0,\"uca\":232.028,\"pa\":0,\"time\":\"20220322202100\",\"isend\":\"0\"},{\"id\":\"3701211100108\",\"pb\":0,\"pc\":0,\"zyggl\":0.0009,\"qa\":0.0073,\"qb\":0,\"time\":\"20220322202100\",\"isend\":\"0\"},{\"id\":\"3701211100108\",\"qc\":0,\"zwggl\":0.002,\"sa\":0.0035,\"sb\":0,\"sc\":0,\"time\":\"20220322202100\",\"isend\":\"0\"},{\"id\":\"3701211100108\",\"zszgl\":0.0049,\"pfa\":0.621,\"pfb\":0,\"pfc\":0,\"zglys\":0.621,\"f\":49.93,\"time\":\"20220322202100\",\"isend\":\"0\"},{\"id\":\"3701211100108\",\"unb\":0.999,\"inb\":0,\"pdm\":-0.0004,\"qdm\":0.0003,\"sdm\":0.0015,\"ig\":2039.708,\"time\":\"20220322202100\",\"isend\":\"1\"}]","payloadType":"json","x":130,"y":60,"wires":[["db4a76e4.7f2978"]]},{"id":"db4a76e4.7f2978","type":"split","z":"bf9e1e33.030598","name":"simulate incoming messages","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","x":370,"y":60,"wires":[["38184dee.eb7bb2"]]},{"id":"38184dee.eb7bb2","type":"switch","z":"bf9e1e33.030598","name":"","property":"payload.isend","propertyType":"msg","rules":[{"t":"eq","v":"1","vt":"str"},{"t":"else"}],"checkall":"true","repair":false,"outputs":2,"x":190,"y":100,"wires":[["64329005.9ef378"],["cda82e63.9c89a"]]},{"id":"64329005.9ef378","type":"change","z":"bf9e1e33.030598","name":"","rules":[{"t":"set","p":"complete","pt":"msg","to":"true","tot":"bool"}],"action":"","property":"","from":"","to":"","reg":false,"x":410,"y":100,"wires":[["cda82e63.9c89a"]]},{"id":"cda82e63.9c89a","type":"join","z":"bf9e1e33.030598","name":"","mode":"custom","build":"merged","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"39","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":170,"y":160,"wires":[["cb5dd3ae.255a3"]]},{"id":"cb5dd3ae.255a3","type":"debug","z":"bf9e1e33.030598","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":580,"y":160,"wires":[]}]

You then have all the data in one message and can construct your sql query.
p.s.
The MQTT node can output a parse json object if you select it in the config, no need for json node.

5

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.