Tasmota, JSON function and split Node

That sounds good to me.

Ok, I structured the data like you suggest.
Measurement is the value what I wanted to search like

YieldDay
YieldTotal
U_AC
I_AC
P_AC
F_AC
Temp
P_DC
Efficiency

Then there is a tag what inverter or what modul it is.
At the end the field is defined by the unit of the stored value. Is this a good idea?

Ok now how should I configurate the nodes to write data like this?

What is the benefit of including that? Is it not always the same?
To reformat the data then personally I would write a simple Function, something like

let fields = {}
let tags = {}
fields.U_AC = msg.payload.U_AC
fields.I_AC = msg.payload.I_AC
..
..
tags.inverter = whatever
tags.anythingelse = ...
...
msg.payload = [fields, tags]
return msg

There are more compact ways of writing it, but the simple way that is easy to follow is probably a good start.

1 Like

Thank you @Colin for this function. I could follow that you are stated there.

Perhaps there could be a misunderstanding.
There is no benefit to have the unit as the fieldkey. The thing is that there have to be a field at least.
When we look at the line protocoll of influx, a tag is not necessary but a field must to store the value.
Like: YieldDay,Inverter=1.1_Ost Wh=500
How to storge the value without fieldkey "Wh" --> I assume that there will be only value, but thats not handy.
But yes it is always the same. I am comming from the science and it is quite scary for me to write a value without any unit, except there is no dimension of the value like pH value.

I will try your function and how I could adapted to my needs.

I don't understand what you mean. Perhaps I misunderstood what you meant by:

Could you give an example of what this data looks like?

I'll try to explain it again on the line protocol and start in general so that we are on the same level.
We need a measurment, a timestamp, a "fieldKey" and if we want a tagset consisting of none, one or more "tagkeys".

The measurement is defined in the InfluxDB node as well as the timestamp. So we don't need to worry about that.
That leaves the "tagkeys" and the "fieldkeys".

The tagkeys are there to build a query and structure data. There I can let off steam and assign as many tagkeys and tagvalues as I want.
Example:
location=west,inverter=2,line=upper,phase=three,azimuth=28

This requires that other data use the same tagkeys in the best case, but with different tagvalues.

The field value is always the measured value, i.e. the number that is to be saved, and a field value must always be specified, otherwise there is always only the word "value" in the database, which is not very helpful. Therefore, the "pair" "fieldkey"="fieldvalue" must always be used.
I cannot save a measurement value/number without a fieldkey.

You can also work with several measured values in the fieldset with several fieldkeys and fieldvalues, but this requires that you save different measured values that are created at the same time with the same tagset.

In relation to my requirement, this means.
YieldDay,Inverter=1.1_East Wh=500

I have the parameter YieldDay under Measurement and this comes from the inverter [inverter here now as tagkey] "1.1_Ost" [this is now the fieldvalue] for the query, with the unit Wh [Wh now as fieldkey] and the measured value 500 [fieldvalue].
I can't save this structure without a fieldkey and I can't think of anything other than taking the unit as a fieldkey.

Technically, I could also leave out the tagset [i.e. Inverter=1.1_Ost], but then I would jump directly from the measurement "YieldDay" to the measured values without knowing where the measured value came from and what it means.

I hope this wasn't too long, but I can discuss it better this way than giving abstract examples.

That won't help as I have never used that.

I see what you mean now, though. What you call a fieldkey is what I call the field name, and you want to include the dimensions in the name. That is fine. I thought you meant you wanted to include the dimensions in the field value. If you had a temperature then instead of calling it temperature, you might call it temperature_C. That is ok, Influxdb doesn't mind what names you use for the keys.

I have just looked up the line protocol, and indeed they call the field name the Field Key.

"value" is just the default field name if you don't give it one. In some circumstances that is no problem. If you have a measurement with only one field then probably the measurement would have a meaningful name. For example, suppose that all I measure for each room it temperature. The the obvious name for the Measurement is "temperature". If I also call the field "temperature" then to access the current value I would have to use temperature.temperature, whereas if I leave it at the default then it is temperature.value, which, to me, seems more appropriate.

1 Like

Hello Colin,

now we are on the same level, I think. I looked up the line protocoll documentatoin for InfluxDB 2.6 also and find this statement:

Field set
(Required) All field key-value pairs for the point. Points must have at least one field. Field keys and string values are case-sensitive. Field keys are subject to naming restrictions.

--> Line protocol | InfluxDB OSS v2 Documentation

Sorry that I havent read this before, but as i mentioned I am a chemist and don't normaly work with such topics, thats only for my private interest.

I will try to use your last suggestion, with the easy function and will study more the function note and debug data, to learn how to structure the data like I want.

How ever I think what would be my last post here:
I tried to understand how is my Mqtt payload structured and how I have to manipulate it to add tags to it to store it as I want.
Also I tried to understand and worked out how to use that examples here: node-red-contrib-influxdb (node) - Node-RED

But it is as closed book for me. I don´t get it. Sorry for you time that you wasted here for me.

No, you don't need a function node in order to process mqtt JSON formatted msg from tasmota.
You'll need a split node and a switch node, which is configured in a special way.
The split node will split each JSON object (a key:value pair) into msgs, containing the KEY (aka object NAME) as the msg.topic and the value into the msg.payload.
Now, understand that with tasmota, the JSON msgs are cascaded and can contain many levels of JSON objects.
The Trick is to feed each higher level back into the split node until the KEY (topic) that needs to be extracted is found and forwared to the next node in the flow
Here is an example flow frrom a tasmota Smart plug with Energy Info:

[{"id":"ad5b96ae50e802b6","type":"tab","label":"TiefkĂźhlschrank","disabled":false,"info":"","env":[]},{"id":"7768a3ce4bfa2fe1","type":"mqtt in","z":"ad5b96ae50e802b6","name":"","topic":"tele/SP111-3/SENSOR","qos":"2","datatype":"json","broker":"1701f33596afcf6b","nl":false,"rap":true,"rh":0,"inputs":0,"x":140,"y":160,"wires":[["479cf462b52a2528"]]},{"id":"479cf462b52a2528","type":"split","z":"ad5b96ae50e802b6","name":"","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"topic","x":370,"y":160,"wires":[["e8f807d14ed2de99"]]},{"id":"e8f807d14ed2de99","type":"switch","z":"ad5b96ae50e802b6","name":"","property":"topic","propertyType":"msg","rules":[{"t":"eq","v":"ENERGY","vt":"str"},{"t":"eq","v":"Power","vt":"str"},{"t":"eq","v":"Total","vt":"str"},{"t":"eq","v":"Yesterday","vt":"str"},{"t":"eq","v":"Today","vt":"str"}],"checkall":"false","repair":false,"outputs":5,"x":490,"y":280,"wires":[["479cf462b52a2528"],["2d4c88bd1def7ccb"],["34025985bf75f336"],["011745741c429cd7"],["2f29a20115dbdcd3"]]},{"id":"2d4c88bd1def7ccb","type":"change","z":"ad5b96ae50e802b6","name":"set topic TK/Energy/Power","rules":[{"t":"set","p":"topic","pt":"msg","to":"TK/Energy/Power","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":900,"y":260,"wires":[["17335030b5662359"]]},{"id":"17335030b5662359","type":"mqtt out","z":"ad5b96ae50e802b6","name":"MQTT.10.22","topic":"","qos":"2","retain":"false","respTopic":"","contentType":"","userProps":"","correl":"","expiry":"","broker":"1701f33596afcf6b","x":1250,"y":320,"wires":[]},{"id":"34025985bf75f336","type":"change","z":"ad5b96ae50e802b6","name":"set topic TK/Energy/Total","rules":[{"t":"set","p":"topic","pt":"msg","to":"TK/Energy/Total","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":890,"y":300,"wires":[["17335030b5662359"]]},{"id":"011745741c429cd7","type":"change","z":"ad5b96ae50e802b6","name":"set topic TK/Energy/Yesterday","rules":[{"t":"set","p":"topic","pt":"msg","to":"TK/Energy/Yesterday","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":910,"y":340,"wires":[["17335030b5662359"]]},{"id":"2f29a20115dbdcd3","type":"change","z":"ad5b96ae50e802b6","name":"set topic TK/Energy/Today","rules":[{"t":"set","p":"topic","pt":"msg","to":"TK/Energy/Today","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":890,"y":380,"wires":[["17335030b5662359"]]},{"id":"1701f33596afcf6b","type":"mqtt-broker","name":"mosquitto-on-alarm","broker":"192.168.10.22","port":"1883","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":""}]

In your example, the first result of the split node will create two messages

  • msg.topic "Time" (msg.payload "2023-03-01T21:22:24"

  • msg.topic Strom" (msg.payload: "{Total_in":1670.395,"Total_out":99.220,"Power_cur":325}
    ..so the 2nd msg.payload is another JSON massage....feed the topic "Strom" (like in my example "ENERGY" ) back into the split node with the switch node....the split node wiill then create 3 additional, new messages

  • msg.topic "Total_in" (msg.paylod: "670.395"

  • msg.topic Total_out (msg.payload ":99.220)

  • msg.topic "Power_cur" (msg.payload "325")

...the trick with the switch node then is to keep the sequence of the topics in order....feed the higher levels (topics with JSON as payload) back into the split node, feed the desired last level topics (topics with the real value as payload) into the next flow-node. AND set the switch node to stop after the first match !!

With that structure, you should be able to use these msgs for feeding an influxDB node easily.

hope this helps....don't give up so easily :wink:

1 Like

Good morning hominidae: Thanks for your post.
The initial question with the data from the smart meter is already solved with the help vom colin.

I ask a second question (I know not ideal) regarding how to add tags to another mqtt msg for storing this value in a structured way in influxdb 2.0.

I found a way to inject a random number and adding tags.

[
    {
        "id": "d21bc07bf060b20a",
        "type": "inject",
        "z": "5cfd3e546a16dc43",
        "name": "",
        "props": [
            {
                "p": "payload",
                "v": "",
                "vt": "date"
            },
            {
                "p": "topic",
                "v": "",
                "vt": "str"
            }
        ],
        "repeat": "",
        "crontab": "",
        "once": false,
        "topic": "",
        "payload": "",
        "payloadType": "date",
        "x": 480,
        "y": 200,
        "wires": [
            [
                "164a0735ac9deb4e",
                "c831763bd6dd221d"
            ]
        ]
    },
    {
        "id": "164a0735ac9deb4e",
        "type": "function",
        "z": "5cfd3e546a16dc43",
        "name": "Fields and Tags",
        "func": "msg.payload = [{\n      W: Math.random()*10,\n},\n{\n    Inverter:\"sensor1\",\n    Modul:\"device2\"\n}];\nreturn msg;",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 640,
        "y": 200,
        "wires": [
            [
                "0caceeb0978d0ee0",
                "7d0d216c9b63a854"
            ]
        ]
    },
    {
        "id": "0caceeb0978d0ee0",
        "type": "influxdb out",
        "z": "5cfd3e546a16dc43",
        "influxdb": "",
        "name": "",
        "measurement": "Measurment",
        "precision": "ms",
        "retentionPolicy": "",
        "database": "test",
        "precisionV18FluxV20": "ms",
        "retentionPolicyV18Flux": "",
        "org": "IfDB2.0_WH",
        "bucket": "test",
        "x": 860,
        "y": 200,
        "wires": []
    },
    {
        "id": "c831763bd6dd221d",
        "type": "debug",
        "z": "5cfd3e546a16dc43",
        "name": "debug 6",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "true",
        "targetType": "full",
        "statusVal": "",
        "statusType": "auto",
        "x": 660,
        "y": 320,
        "wires": []
    },
    {
        "id": "7d0d216c9b63a854",
        "type": "debug",
        "z": "5cfd3e546a16dc43",
        "name": "debug 7",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "true",
        "targetType": "full",
        "statusVal": "",
        "statusType": "auto",
        "x": 820,
        "y": 380,
        "wires": []
    }
]

This nodes inject a timestamp and create a random number. The number will be written in the measurement "measurement" and in the bucket "test" in influx. For the structering and helping to build queries I add in the function the tags "Inverters:Allinverters" and "Modul:device2".

The only thing i have to solve is how to take the mqtt payload in this flow instead of the random number.

Here are the debugs from my mqtt messages:

Smart Meter:

24.3.2023, 07:55:20node: Total_in
tele/Stromzaehler/SENSOR : msg.payload : number
1803.44

Solar plant:

Solaranlage/total/P_AC : msg : Object
object
topic: "Solaranlage/total/P_AC"
payload: 170.8
qos: 0
retain: true
_msgid: "08cdf1dde31504c0"

So are you trying to combine the information from those two mqtt msgs into one msg to send to Influx? If so you will need to use a join node to get the two msgs into a combined msg and then you can format it the way you need it.

1 Like

Not sure if there is a difference how influxDB 2.0 and influxDB 1.8 are handled in the influxDB node.
I am currrently using influxDB 1.8 still, for a special reason.
The influxDB node would normally accept a msg, formed as an array of two objects, where the first is the named field ("value", "time") and the second is the named "topic".
Easy to guess what part of the incoming message need to go where in that structure.
When using in a change node, after the switch node, that sends msgs for each individual topic, you can directly connnect that change node to the influxDB node.

I am using a simple change node with an JSONATA expression to form the msg suitable for the influxDB node.
With your incoming msgs, containing msg.topic and msg.payload, and assuming no time part, so we would be using Time from "$now in milliseconds for influxDB, it should looks like this:
image
...with the JSONATA expression inside looking like this:
image

...in the influxDB node, set the time precision to Milliseconds

1 Like

Just as a side note, you know that there is a simpler method, outsde of Node-Red to fetch data from mqtt and store it in iinfluxDB, don't you?
As you obviously do have an mqtt server running, look at a tool named "telegraf"...it is part of the TIG (Tegraf, Influx, Grafana) Stack ... here is an example, even in German: Monitoring mit Telegraf, InfluxDB und Grafana – bachmann-lan.de

I am using telegraf for all of my mqtt data.
Sometimes, like for tasmota JSON msgs, I am using Node-red to convert the tasmota topics into plain, simple, seperated topics, back into the mqtt Server, then instruct telegraf to fetch the data from the plain topic-tree (like in my Flow above, with the split/switch node).
Much easier and less ressources used.

1 Like

At the end I focused again on this here: node-red-contrib-influxdb (node) - Node-RED

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.

For example, the following simple flow uses an InfluxDb 2.0 database and injects four fields as above, along with two tags, tag1 and tag2:

With some try and error (without understanding what I am doing exactly) it worked.

Here are my flow of one example:

[
    {
        "id": "2f3b32c497a92189",
        "type": "mqtt in",
        "z": "cb1f24ef01a08834",
        "name": "Leistung AC [W]",
        "topic": "Solaranlage/total/P_AC",
        "qos": "0",
        "datatype": "auto-detect",
        "broker": "ad8500bb94c6b51e",
        "nl": false,
        "rap": true,
        "rh": 0,
        "inputs": 0,
        "x": 100,
        "y": 260,
        "wires": [
            [
                "e0d4de7935c26eb2",
                "6d46cb13ee6148a2",
                "662ee787dced6a40"
            ]
        ]
    },
    {
        "id": "662ee787dced6a40",
        "type": "function",
        "z": "cb1f24ef01a08834",
        "name": "Fields and Tags",
        "func": "msg.payload = [{\n    W : msg.payload\n},\n{\n    Inverter:\"AllInverters\",\n    \n}];\nreturn msg;",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 320,
        "y": 260,
        "wires": [
            [
                "93df5583153b519c"
            ]
        ]
    },
    {
        "id": "93df5583153b519c",
        "type": "influxdb out",
        "z": "cb1f24ef01a08834",
        "influxdb": "58532709ad6e5cb3",
        "name": "DB",
        "measurement": "P_AC [W]",
        "precision": "",
        "retentionPolicy": "",
        "database": "database",
        "precisionV18FluxV20": "s",
        "retentionPolicyV18Flux": "",
        "org": "IfDB2.0_WH",
        "bucket": "PV-Anlage",
        "x": 490,
        "y": 260,
        "wires": []
    },
    {
        "id": "ad8500bb94c6b51e",
        "type": "mqtt-broker",
        "name": "MQTT Broker rpi",
        "broker": "192.168.178.136",
        "port": "1883",
        "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": "58532709ad6e5cb3",
        "type": "influxdb",
        "hostname": "127.0.0.1",
        "port": "8086",
        "protocol": "http",
        "database": "database",
        "name": "InfluxDB2.0 rpi",
        "usetls": false,
        "tls": "d50d0c9f.31e858",
        "influxdbVersion": "2.0",
        "url": "http://192.168.178.136:8086",
        "rejectUnauthorized": true
    },
    {
        "id": "d50d0c9f.31e858",
        "type": "tls-config",
        "name": "",
        "cert": "",
        "key": "",
        "ca": "",
        "certname": "",
        "keyname": "",
        "caname": "",
        "servername": "",
        "verifyservercert": false
    }
]

There is a very big difference. Influx 2.0 has a frontend with UI. There are no databases, there are organizations and buckets and it uses API Tokens for access.
One major diffrence is also that you can use tags do structure your data in the database. Dashboards in InfluxDB are also possible.

When you using grafana do builed you query in InfluxDB and copy it to grafana. There are no interface in grafana used to click your data together from influxDB 2.0. You have to build a query in Flux language.
So all in all InfluxDB 1.8 and 2.0 are not compatible.

Why I am not using telefgraf?
Yes at the beginning I tried, because that the I think normal approach, but I was not able to configurate the telegrafe agent. Additionally I was not sure if it has to be a stande alone container in docker or was it running in InfluxDB itself or where I have to configurate it. All this programms there I have to configurate a config data in a specific directory without any visual help or at least some hints if this correct or not, is very difficult for me when I dont unterstand in detail what I am doing.
I saw a video on youtube where node red was used for this and from the first impression that was much easier for me, because it worked from the first try. Not like I wanted it but it worked. This felt more successfully also with the help of the UI from node red and the nodes itself.

Thank you very much that you helped an noob like me to not giving up. :slight_smile:

1 Like

Yes, that description is the same, regardless wether influxDb v1.8 oder 2.0 is used.

Congrats, that you figured it out at the end.

Thanks for the Info.
I have a large setup with influxDB1.8 on my NAS so far...just to scared to try a migration :wink:

Thanks again, Now I am tempted to try it :wink:

You're welcome. I am a noob also, when it comes to javascript (I am too old for that stuff)..that's why I always try to avoid using a function node at all cost....so far, this did work fine...nothing I couldn't do with a combo of the other nodes, in a more like "no-coding" fashion....that's why I love Node-red.

Keep up the good spirit! Stay safe!

1 Like

...separate App...in a Docker is what I am using

Yes, I can understand that.
What I mostly do is, after I had my best sets of telegraf config patternns running, I am using Node-Red to convert other msg formats into one of the pattern that works with telegraf (feeding these back into my MQTT-broker under a different topic. So in the telegraf config, I only have to add new topics to the list, that need to be fetched and stored.

How is that different to using tags with 1.8?
I know the underlying structure is different but from the users point of view what is the difference?

2 Likes

What I have seen so far is that people using InfluxDB 1.8 to push all data in one database without any tagging because there is not GUI for 1.8.
From my (nooby) point of view queries are only make seens if you can use them in a GUI. Except you have another tool that are building the queries to pull data out of influx.

That is only my restricted impression. It was also for me a difficult decision to use 1.8 or 2.0. I think the overall internet support and what you can find (additional panels for grafana) is more used with 1.8.

I deciced to go with 2.0 because because it is the latest and easier to look up because it has a GUI.