Tasmota, JSON function and split Node

Hello, I am new to all this stuff and have never work with databases (only tiny commands in mysql at work) and never have used Node-Red before, but I need help, because it can not be so difficult. I used search and Chatgpt but did not understand what I have to do for solving.

Main: I have a solarpanels and the inverter send a lot of data do a data transfer unit whith send all this data via MQTT. I want to store it in a database (InfluxDB).
I used Node-Red to put all the data in a database (or buckets). Every send value has its own MQTT Topic so the node is very easy.
See here: solar hosted at ImgBB — ImgBB
There are much more values but I grouped them an put them in one bucket with every value have its own maesurment.
In the end I want to visualize with grafana, but one by one.

I have also a energy meter where you could read out three values and send it via MQTT.
The MQTT message is like that:
tele/Stromzaehler/SENSOR
{"Time":"2023-03-01T21:22:24","Strom":{"Total_in":1670.395,"Total_out":99.220,"Power_cur":325}}
One Topic three values in json.
I looked for help via google and found out that I need funktion node and split node and then three Influx nodes. --> Energy-Meter hosted at ImgBB — ImgBB
I asked chatgpt what I have to enter in the function node.
It gave me the code:

var totalIn = msg.payload.Strom.Total_in;
var totalOut = msg.payload.Strom.Total_out;
var powerCur = msg.payload.Power_cur;

msg.totalIn = totalIn;
msg.totalOut = totalOut;
msg.powerCur = powerCur;

return msg;

But I got this in debug:

tele/Stromzaehler/SENSOR : msg.payload1 : undefined

I asked chatgpt again, it gave me an answer but I don`t know what I have to do. I search more in the internet but don´t get the point what to change. I could be easy, but I have no idea.

Could somebody help me or explain a little. I don`t even know if payload is a variable or only a name or something that has to stated there in the MQTT.

Hi @agentsmith1612 and welcome to Node-RED community.
In the DEBUG node try to change output property:
msg.payload
whitout number 1. May be you used this new property before, but by default is only msg.payload.

I just try to simulate your example in this flow:

[{"id":"61768003a72b3f27","type":"tab","label":"Flow 28","disabled":false,"info":"","env":[]},{"id":"4ccb71b73d49e733","type":"inject","z":"61768003a72b3f27","name":"MQTT simulation","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"tele/Stromzaehler/SENSOR","payload":"{\"Time\":\"2023-03-01T21:22:24\",\"Strom\":{\"Total_in\":1670.395,\"Total_out\":99.220,\"Power_cur\":325}}","payloadType":"json","x":320,"y":240,"wires":[["98bda65dd9ab803c"]]},{"id":"98bda65dd9ab803c","type":"function","z":"61768003a72b3f27","name":"function 30","func":"var totalIn = msg.payload.Strom.Total_in;\nvar totalOut = msg.payload.Strom.Total_out;\nvar powerCur = msg.payload.Power_cur;\n\nmsg.totalIn = totalIn;\nmsg.totalOut = totalOut;\nmsg.powerCur = powerCur;\n\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":490,"y":240,"wires":[["aaa97941ec3948d3"]]},{"id":"aaa97941ec3948d3","type":"debug","z":"61768003a72b3f27","name":"debug 45","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":640,"y":240,"wires":[]}]

Also look a this Node-RED documentation: Working with messages.

I hope this can help you!!

1 Like

Why don't you post your flow source data here?
That's easier to analyze than a number of pictures...

Afer applying your hint and activating debug 2 (after split) I got this (it looks like know there are two messages but spit in time and the three values?)

.3.2023, 21:48:04[node: debug 2](http://192.168.178.245:49156/#)tele/Stromzaehler/SENSOR : msg.payload : string[19]

"2023-03-01T21:48:04"

1.3.2023, 21:48:04[node: debug 2](http://192.168.178.245:49156/#)

tele/Stromzaehler/SENSOR : msg.payload : Object

{ Total_in: 1670.48, Total_out: 99.22, Power_cur: 151 }

What do you mean "using a new" property before?
I can´t follow your example. I hope you don´t kill me, when I said that I don´t know that is type, tab, lable, flow ..........

@ralphwetzel
Because I thought that this is a way to see what I have did. I did not know that copying the flow in text was possible.
All this help and documentation is for me like, when I gave you a book of anorganic and analytical chemistry. I hope you don´t kill me because of my (silly) questions.

What I only want is that the mqtt data from tasmota will be written in the influx, like the datas from my solar inverter:

[
    {
        "id": "ffb671a9831802b9",
        "type": "mqtt in",
        "z": "f6f2187d.f17ca8",
        "name": "Spannung_AC [V]",
        "topic": "",
        "qos": "0",
        "datatype": "auto-detect",
        "broker": "2dd28f61901aa014",
        "nl": false,
        "rap": true,
        "rh": 0,
        "inputs": 0,
        "x": 993,
        "y": 580,
        "wires": [
            [
                "63865b3f6b2ad4e9",
                "a397676e16f5e7e3"
            ]
        ]
    },
    {
        "id": "63865b3f6b2ad4e9",
        "type": "influxdb out",
        "z": "f6f2187d.f17ca8",
        "influxdb": "b27ce1f4be5b0412",
        "name": "DB",
        "measurement": "Spannung_AC [V]",
        "precision": "",
        "retentionPolicy": "",
        "database": "database",
        "precisionV18FluxV20": "s",
        "retentionPolicyV18Flux": "",
        "org": "Strom",
        "bucket": "Solar_2_Süd",
        "x": 1193,
        "y": 580,
        "wires": []
    },
    {
        "id": "2dd28f61901aa014",
        "type": "mqtt-broker",
        "name": "MQTT Broker",
        "broker": "192.168.178.245",
        "port": "1886",
        "clientid": "",
        "autoConnect": true,
        "usetls": false,
        "protocolVersion": "4",
        "keepalive": "60",
        "cleansession": true,
        "birthTopic": "",
        "birthQos": "0",
        "birthPayload": "",
        "birthMsg": {},
        "closeTopic": "",
        "closeQos": "0",
        "closePayload": "",
        "closeMsg": {},
        "willTopic": "",
        "willQos": "0",
        "willPayload": "",
        "willMsg": {},
        "userProps": "",
        "sessionExpiry": ""
    },
    {
        "id": "b27ce1f4be5b0412",
        "type": "influxdb",
        "hostname": "127.0.0.1",
        "port": "8086",
        "protocol": "http",
        "database": "database",
        "name": "Influx DB2",
        "usetls": false,
        "tls": "",
        "influxdbVersion": "2.0",
        "url": "http://192.168.178.245:8086",
        "rejectUnauthorized": true
    }
]

I recommend you to take a look at the documentation I shared in the previous post to understand the messages in Node-RED.

The bug that you had originally was solved, because it already shows you the values, now what you have to do is to separate them individually to send them to an influx node, as you said.

I share with you this example flow, based on your information, so you can see how you can get the separated messages.
Screens
You can get this:
Scre

The flow example (You can found here how to importing flow):

[{"id":"f77c27afa319db39","type":"tab","label":"Flow 28","disabled":false,"info":"","env":[]},{"id":"144257a53df292f4","type":"inject","z":"f77c27afa319db39","name":"MQTT simulation","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"tele/Stromzaehler/SENSOR","payload":"{\"Time\":\"2023-03-01T21:22:24\",\"Strom\":{\"Total_in\":1670.395,\"Total_out\":99.220,\"Power_cur\":325}}","payloadType":"json","x":320,"y":260,"wires":[["66fb433d11874e34"]]},{"id":"66fb433d11874e34","type":"function","z":"f77c27afa319db39","name":"function 30","func":"var totalIn = msg.payload.Strom.Total_in;\nvar totalOut = msg.payload.Strom.Total_out;\nvar powerCur = msg.payload.Power_cur;\n\nmsg.totalIn = totalIn;\nmsg.totalOut = totalOut;\nmsg.powerCur = powerCur;\n\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":490,"y":260,"wires":[["064319bce370768b","a19421bd63238729","ba3a5b4eb5a210b9"]]},{"id":"c196bf3cafe0e944","type":"debug","z":"f77c27afa319db39","name":"Total_in","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":860,"y":220,"wires":[]},{"id":"f6f306dd282c5042","type":"debug","z":"f77c27afa319db39","name":"Total_out","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":860,"y":260,"wires":[]},{"id":"544e98d8a8aa5dbb","type":"debug","z":"f77c27afa319db39","name":"Power_cur","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":870,"y":300,"wires":[]},{"id":"064319bce370768b","type":"change","z":"f77c27afa319db39","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"payload.Strom.Total_in","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":680,"y":220,"wires":[["c196bf3cafe0e944"]]},{"id":"a19421bd63238729","type":"change","z":"f77c27afa319db39","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"payload.Strom.Total_out","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":680,"y":260,"wires":[["f6f306dd282c5042"]]},{"id":"ba3a5b4eb5a210b9","type":"change","z":"f77c27afa319db39","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"payload.Strom.Power_cur","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":680,"y":300,"wires":[["544e98d8a8aa5dbb"]]}]
1 Like

Thank you very much.
Now I understand import and export, so it is very easy, like in other software.
By the way, English is not may native language, perhaps you have already recognize this in my flow naming.

Well, yes that is what I wanted. Three seperate values that I can store in each in one measurement in influx.

Is it right that function only works with the change node, because I took a debug right after the function 30 to see that the node does but it changed nothing, or nothing that I can see. Only after the change note there are a change, right?

1 Like

You need to set the debug node (right after the function node) to Output complete msg object to see what the function node does.
What it does is not really necessary ... but you'll get the point.

2 Likes

Additional comment:

You'll see as well that there's a bug in the function node:

msg.payload.Power_cur doesn't exist...
It's msg.payload.Strom.Power_cur !

1 Like

Great!

Yes, It is very easy to work with Node-RED (I recommend reviewing the node-red documentation whenever possible, it is very well explained and it will help you to better exploit the tool for your future projects). English is not my native language (spanish), but all the info is in english, so.

Yes, there are many nodes with a specific function, that help us to do it more easy. The function node allows you to add javascript code to create functions, but there is almost always a node that already does that function. You could check this documentation, it explains it better than me:

The Core Nodes.

Cheers (Saludos).

2 Likes

Hello everybody, I worked again at my nodes and InfluxDB. I got a good Example how to change data for wrinting it in Influx.
Like here:

[
    {
        "id": "c593b69ee7f352bd",
        "type": "mqtt in",
        "z": "f6f2187d.f17ca8",
        "name": "Leistung AC [W]",
        "topic": "Solaranlage/total/P_AC",
        "qos": "0",
        "datatype": "auto-detect",
        "broker": "2dd28f61901aa014",
        "nl": false,
        "rap": true,
        "rh": 0,
        "inputs": 0,
        "x": 100,
        "y": 320,
        "wires": [
            [
                "ca8cac0a5b8c8c1f",
                "d4288cfb4ef11ee0"
            ]
        ]
    },
    {
        "id": "d4288cfb4ef11ee0",
        "type": "change",
        "z": "f6f2187d.f17ca8",
        "name": "",
        "rules": [
            {
                "t": "move",
                "p": "payload",
                "pt": "msg",
                "to": "msg.payload.P_AC",
                "tot": "msg"
            },
            {
                "t": "set",
                "p": "msg.payload.Total P_AC",
                "pt": "msg",
                "to": "",
                "tot": "str"
            }
        ],
        "action": "",
        "property": "",
        "from": "",
        "to": "",
        "reg": false,
        "x": 390,
        "y": 320,
        "wires": [
            [
                "c32ca859d36df620",
                "8916682ef237a5e6"
            ]
        ]
    },
    {
        "id": "c32ca859d36df620",
        "type": "influxdb out",
        "z": "f6f2187d.f17ca8",
        "influxdb": "b27ce1f4be5b0412",
        "name": "P_AC_DB",
        "measurement": "Total",
        "precision": "",
        "retentionPolicy": "",
        "database": "database",
        "precisionV18FluxV20": "s",
        "retentionPolicyV18Flux": "",
        "org": "Strom",
        "bucket": "test5",
        "x": 640,
        "y": 320,
        "wires": []
    },
    {
        "id": "2dd28f61901aa014",
        "type": "mqtt-broker",
        "name": "MQTT Broker",
        "broker": "192.168.178.245",
        "port": "1886",
        "clientid": "",
        "autoConnect": true,
        "usetls": false,
        "protocolVersion": "4",
        "keepalive": "60",
        "cleansession": true,
        "birthTopic": "",
        "birthQos": "0",
        "birthPayload": "",
        "birthMsg": {},
        "closeTopic": "",
        "closeQos": "0",
        "closePayload": "",
        "closeMsg": {},
        "willTopic": "",
        "willQos": "0",
        "willPayload": "",
        "willMsg": {},
        "userProps": "",
        "sessionExpiry": ""
    },
    {
        "id": "b27ce1f4be5b0412",
        "type": "influxdb",
        "hostname": "127.0.0.1",
        "port": "8086",
        "protocol": "http",
        "database": "database",
        "name": "Influx DB2",
        "usetls": false,
        "tls": "",
        "influxdbVersion": "2.0",
        "url": "http://192.168.178.245:8086",
        "rejectUnauthorized": true
    }
]

Now I try to write data from the MQTT Solar DTU to InfluxDB and want to add tags and fields for influxDB, to structure the data better.
Line Protocoll for InfluxDB to write data:
measurement,tagkey="tagvalue" fieldkey="fieldValue"
Example:
Inverters,Inverter=2.1_Süd U_AC=235
Or
Module,Modul=S.1.4 YieldDay=450

I used to try Change node again but I never get the data where I want to.

Here is my last try of the change node:

[
    {
        "id": "7b13402176c4286c",
        "type": "mqtt in",
        "z": "f6f2187d.f17ca8",
        "name": "Spannung_AC [V]",
        "topic": "Solaranlage/Inverter_2.1_Su/ch0/U_AC",
        "qos": "0",
        "datatype": "auto-detect",
        "broker": "2dd28f61901aa014",
        "nl": false,
        "rap": true,
        "rh": 0,
        "inputs": 0,
        "x": 550,
        "y": 580,
        "wires": [
            [
                "a97564372d02f196",
                "a628c5a505e995d5",
                "2385695c14db7090"
            ]
        ]
    },
    {
        "id": "2385695c14db7090",
        "type": "change",
        "z": "f6f2187d.f17ca8",
        "name": "",
        "rules": [
            {
                "t": "set",
                "p": "measurement",
                "pt": "msg",
                "to": "Inverters",
                "tot": "str"
            },
            {
                "t": "set",
                "p": "payload",
                "pt": "msg",
                "to": "Inverter=2.1_Süd,U_AC",
                "tot": "msg"
            }
        ],
        "action": "",
        "property": "",
        "from": "",
        "to": "",
        "reg": false,
        "x": 790,
        "y": 580,
        "wires": [
            [
                "05722bc5b6ed4055",
                "40a360ed0cfbc885"
            ]
        ]
    },
    {
        "id": "05722bc5b6ed4055",
        "type": "influxdb out",
        "z": "f6f2187d.f17ca8",
        "influxdb": "b27ce1f4be5b0412",
        "name": "DB",
        "measurement": "Inverters",
        "precision": "",
        "retentionPolicy": "",
        "database": "database",
        "precisionV18FluxV20": "s",
        "retentionPolicyV18Flux": "",
        "org": "Strom",
        "bucket": "test5",
        "x": 970,
        "y": 580,
        "wires": []
    },
    {
        "id": "2dd28f61901aa014",
        "type": "mqtt-broker",
        "name": "MQTT Broker",
        "broker": "192.168.178.245",
        "port": "1886",
        "clientid": "",
        "autoConnect": true,
        "usetls": false,
        "protocolVersion": "4",
        "keepalive": "60",
        "cleansession": true,
        "birthTopic": "",
        "birthQos": "0",
        "birthPayload": "",
        "birthMsg": {},
        "closeTopic": "",
        "closeQos": "0",
        "closePayload": "",
        "closeMsg": {},
        "willTopic": "",
        "willQos": "0",
        "willPayload": "",
        "willMsg": {},
        "userProps": "",
        "sessionExpiry": ""
    },
    {
        "id": "b27ce1f4be5b0412",
        "type": "influxdb",
        "hostname": "127.0.0.1",
        "port": "8086",
        "protocol": "http",
        "database": "database",
        "name": "Influx DB2",
        "usetls": false,
        "tls": "",
        "influxdbVersion": "2.0",
        "url": "http://192.168.178.245:8086",
        "rejectUnauthorized": true
    }
]

Honestly I thought I understand the change node, the msg.payload and the functions but it looks like I did not. Could somebody give me a hint where I am wrong?

Please show us what the data coming in looks like and what your flow produces. Also tell us what format you are aiming for.

Incomming Data:

9.3.2023, 16:36:30node: Debug
Solaranlage/Inverter_2.1_Su/ch0/U_AC : msg : Object
object
topic: "Solaranlage/Inverter_2.1_Su/ch0/U_AC"
payload: 236.5
qos: 0
retain: false
_msgid: "70e8b05379bbaa52"

I don´t know exactly how the output should look like. I only know that I have to store it in the measurement "Inverters" with the tagkey="2.1_Süd" and a fieldkey="U_AC"
The measurement is in the influx DB node defined so I only need to add the tagkey to the data. Fieldkey is the same as in my first example like here:

Input:

9.3.2023, 16:43:03node: Debug
Solaranlage/total/P_AC : msg : Object
object
topic: "Solaranlage/total/P_AC"
payload: 142.7
qos: 0
retain: true
_msgid: "c50cc7a6fc409f58"

Output after Change node:

Solaranlage/total/P_AC : msg : Object
object
topic: "Solaranlage/total/P_AC"
qos: 0
retain: true
_msgid: "ba34b6e17e123f91"
payload: object
P_AC: 141.6

I had the idea to configurate it as the line protokoll in InfluxDB. -->

measurementName,tagKey=tagValue fieldKey="fieldValue" 1465839830100400200
--------------- --------------- --------------------- -------------------
       |               |                  |                    |
  Measurement       Tag set           Field set            Timestamp

Timestamp comes from the data itself.
measurementname is defined in the influxdb node
fieldset is defined like in my first example.
and tagset is what I asking for.

Perhaps this setup could not work as the line protocoll?

If you look in the help text for the influxdb out node it says

If msg.payload is an array containing two objects, the first object will be written as the set of named fields, the second is the set of named tags.

Does "2.1_Süd" indicate which inverter it is?
Does "U_AC" indicate a value of something within that inverter? Are there multiple such parameters from one inverter? If so then I think you want both "2.1_Süd" and "U_AC" to be contained in tags. So perhaps you need something like

[
  {value: 141.6},
  {id: "2.1_Süd", param: "U_AC"}
]

Where id is whatever you want the name of the tag to be. It might be location rather than id. Similarly you might want something other than param for that tag name.

@Colin: Yes, "2.1_Süd" is an inverter and "U_AC" is a value from one inverter. There are 9 parameter from each inverter.
"2.1_Süd" is in reality the location of the invter and "U_AC" is what the inverter are producing.

Let me explain what I want to have in my influxDB.

I read that it is recomended to structure the database with fields and tags in a way that sources with identical paramters stay in the same measurement and if you have different parameter use seperate measurements.
So I have two sources of data:
solar plant
and smart/energy meter

The energy meter is easy only 3 parameters. --> Stromzähler as measurement and the 3 values.
The solar plant have 4 individual inverters. The data transfer unit put 4 parameter as a sum of all inverters --> Total as measurement and the 4 values.
The data trensfer unit can read 8 paramter from each inverter 2 inverters are on a south roof, one on west roof and one on East roof --> Inverters as measurement and tag are the inverters and then the 8 values
Also I can read out each modul from each inverter ---> module as measurement and tags indicate each module and then the 6 values.

I simulate the database entries by writing it by hand in a test bucket in InfluxDB. There are screenshots of my structure.

Now I want to fill it under a different test bucket with real data from node-red.




I don't really follow everything you have posted. I don't use influxdb 2 so I don't know what the screenshots are showing. Are you saying that my suggestion for the inverters is not correct?

You don't need to post screenshots, just say what the field and tags are.

Sorry for that.
Your explanation was correct.

For the inverter example:
"2.1_Süd" is the tag and "U_AC" is the field.

That is not what I am suggesting. I am suggesting that U_AC is another tag, and the value is the field. The value is the value for parameter "U_AC" from inverter "2.1_Süd". So the parameter name and inverter id are tags.

If you wanted to have a set of fields called U_AC, P_AC, ... (or whatever) then you would need them all at once to put in one record in the db.

1 Like

It confused me a little bit now.

For definition: When you write the term "value" is it only the number or is the the number with a definition before?

So InfluxDB differentiate between tags and field. Field is the last before the value.
Tags are before the fields.

Yes, there are a lot of fieds like you write but I have also a lot of nodes for each path a own node, with own change node and own InfluxdB node.
This is because every parameter/value has its own topic, so I dont have to use a function to split values (like with the energy meter right in the beginning of the thread).

Yes, it is a very important difference. Tags are (normally) strings and are indexed, which means you can search on them very fast. So you can query for all records for inverter 2.1_Sud and parameter U_AC very efficiently. Fields are usually numeric values. Tags are parametric data that has a defined set of values, telling you things about the data. The fields contain the actual values of things that vary over time.

The confusion can occur when it comes to when the name of a field should actually be a tag. So a common example (a bit easier for me to describe) is the environment in a house. You might have temperature and humidity sensors in each room. Then you have the choice of having the room as a tag (bedroom, kitchen etc) and the temperature and humidity as fields containing those values. So the data for a record might be
{room: "kitchen", temperature: 23, humidity: 65}
If the temperature and humidity arrive at the same time (from a single sensor for example) then that is exactly how I would do it. If, however, they come from different sensors and so arrive at different times (and might have different sample rates) then I would have a Measurement for temperatures, with room as a tag and field called "value" and another Measurement for humidities again with room and value. I would use different Measurements because they hold different types of data. Again, though, suppose I measured the air temperature at floor and ceiling, then I would put all the temperatures in one Measurement, with a tag for room and another tag for position, which would contain floor or ceiling.

So actually I am re-thinking your case, having worked through the example. It depends on what you are actually measuring how I would organise the measurements and tags.

1 Like

Thank Colin for your detailed explanation. Hopefully I got your point. Let me discuss it:

There are at least two types in my example. I structured it like "where are the data are comming from" Your way is "what data you want to look up." Right?

Let´s keep the Energy meter and the sum of all inveters (total) away.

all the parameter with its values (P_AC, U_AC, etc.) are comming from each inverter (like your rooms) at the same time with he same timestamp.

In your way I would create a measurement for the paramters P_AC, U_AC... etc. and then tag is if its from Total, inveter or modul and use second tag for which inverter or modul it is coming from right?

In the end it is not so much important because I will use grafana to visualise, i only want to understand it and making it efficient and useful.