Reorder mqtt messages JK BMS - ESP32 - MQTT - Node RED - Influxdb

General
1

Dear All,

could somebody be so kind helping me with the following node red challenge:
I have a JK BMS care for my batteries. The BMS can be read from ESP32 via Bluetooth. ESP32 reads values from BMS and sends a variety of data via MQTT to node red. From there I choose only some values, join it to 1 message and send it to my local Influx DB.
Basically it works. (sorry to send to code like this, I can not upload whole file).

[
    {
        "id": "b3968456737a18dc",
        "type": "tab",
        "label": "Flow 3",
        "disabled": false,
        "info": "",
        "env": []
    },
    {
        "id": "ea8179c21a5455c4",
        "type": "mqtt in",
        "z": "b3968456737a18dc",
        "name": "",
        "topic": "BMS_Watchdog/Data/Battery_Voltage",
        "qos": "2",
        "datatype": "json",
        "broker": "10e78a89.5b4fd5",
        "nl": false,
        "rap": false,
        "inputs": 0,
        "x": 170,
        "y": 60,
        "wires": [
            [
                "ccfb8a0c9253142f"
            ]
        ]
    },
    {
        "id": "693609f1bb222e12",
        "type": "mqtt in",
        "z": "b3968456737a18dc",
        "name": "",
        "topic": "BMS_Watchdog/Data/Charge_Current",
        "qos": "2",
        "datatype": "json",
        "broker": "10e78a89.5b4fd5",
        "nl": false,
        "rap": false,
        "inputs": 0,
        "x": 170,
        "y": 120,
        "wires": [
            [
                "ccfb8a0c9253142f"
            ]
        ]
    },
    {
        "id": "f8d56f329d4b07d5",
        "type": "mqtt in",
        "z": "b3968456737a18dc",
        "name": "",
        "topic": "BMS_Watchdog/Data/Battery_Power",
        "qos": "2",
        "datatype": "json",
        "broker": "10e78a89.5b4fd5",
        "nl": false,
        "rap": false,
        "inputs": 0,
        "x": 160,
        "y": 180,
        "wires": [
            [
                "ccfb8a0c9253142f"
            ]
        ]
    },
    {
        "id": "a7bb2f3dba4444fb",
        "type": "change",
        "z": "b3968456737a18dc",
        "name": "index = 2",
        "rules": [
            {
                "t": "set",
                "p": "index",
                "pt": "msg",
                "to": "2",
                "tot": "num"
            }
        ],
        "action": "",
        "property": "",
        "from": "",
        "to": "",
        "reg": false,
        "x": 200,
        "y": 380,
        "wires": [
            []
        ]
    },
    {
        "id": "ccfb8a0c9253142f",
        "type": "join",
        "z": "b3968456737a18dc",
        "name": "",
        "mode": "custom",
        "build": "object",
        "property": "payload",
        "propertyType": "msg",
        "key": "topic",
        "joiner": "\\n",
        "joinerType": "str",
        "useparts": false,
        "accumulate": false,
        "timeout": "",
        "count": "3",
        "reduceRight": false,
        "reduceExp": "",
        "reduceInit": "",
        "reduceInitType": "num",
        "reduceFixup": "",
        "x": 430,
        "y": 120,
        "wires": [
            [
                "4e9be2c79756f9c2",
                "3b609176e0cbd5a5"
            ]
        ]
    },
    {
        "id": "d35f1d2fcd78e4a4",
        "type": "sort",
        "z": "b3968456737a18dc",
        "name": "",
        "order": "ascending",
        "as_num": false,
        "target": "index",
        "targetType": "msg",
        "msgKey": "payload",
        "msgKeyType": "elem",
        "seqKey": "index",
        "seqKeyType": "msg",
        "x": 190,
        "y": 420,
        "wires": [
            []
        ]
    },
    {
        "id": "67e3729726d5c969",
        "type": "change",
        "z": "b3968456737a18dc",
        "name": "index = 0",
        "rules": [
            {
                "t": "set",
                "p": "index",
                "pt": "msg",
                "to": "0",
                "tot": "num"
            }
        ],
        "action": "",
        "property": "",
        "from": "",
        "to": "",
        "reg": false,
        "x": 200,
        "y": 300,
        "wires": [
            []
        ]
    },
    {
        "id": "4e9be2c79756f9c2",
        "type": "debug",
        "z": "b3968456737a18dc",
        "name": "",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "true",
        "targetType": "full",
        "statusVal": "",
        "statusType": "auto",
        "x": 590,
        "y": 120,
        "wires": []
    },
    {
        "id": "a9f89eccb802ba81",
        "type": "change",
        "z": "b3968456737a18dc",
        "name": "index = 1",
        "rules": [
            {
                "t": "set",
                "p": "index",
                "pt": "msg",
                "to": "1",
                "tot": "num"
            }
        ],
        "action": "",
        "property": "",
        "from": "",
        "to": "",
        "reg": false,
        "x": 200,
        "y": 340,
        "wires": [
            []
        ]
    },
    {
        "id": "3b609176e0cbd5a5",
        "type": "influxdb out",
        "z": "b3968456737a18dc",
        "influxdb": "89639b27036bb47e",
        "name": "ralftzeDB",
        "measurement": "klima",
        "precision": "ms",
        "retentionPolicy": "",
        "database": "database",
        "precisionV18FluxV20": "ms",
        "retentionPolicyV18Flux": "",
        "org": "organisation",
        "bucket": "bucket",
        "x": 600,
        "y": 180,
        "wires": []
    },
    {
        "id": "10e78a89.5b4fd5",
        "type": "mqtt-broker",
        "name": "",
        "broker": "localhost",
        "port": "1883",
        "clientid": "",
        "autoConnect": true,
        "usetls": false,
        "protocolVersion": 4,
        "keepalive": "60",
        "cleansession": true,
        "autoUnsubscribe": true,
        "birthTopic": "",
        "birthQos": "0",
        "birthPayload": "",
        "birthMsg": {},
        "closeTopic": "",
        "closeQos": "0",
        "closePayload": "",
        "closeMsg": {},
        "willTopic": "",
        "willQos": "0",
        "willPayload": "",
        "willMsg": {},
        "userProps": "",
        "sessionExpiry": ""
    },
    {
        "id": "89639b27036bb47e",
        "type": "influxdb",
        "hostname": "192.168.2.141",
        "port": 8086,
        "protocol": "http",
        "database": "ralftzeDB",
        "name": "nachralftzeDB",
        "usetls": false,
        "tls": "",
        "influxdbVersion": "1.x",
        "url": "http://localhost:8086",
        "timeout": 10,
        "rejectUnauthorized": true
    }
]

but there's one thing where I actually spent hours and hours but can't find a solution:
I would like to put the 3 parameters into an defined order:
1 Voltage, 2 Current, 3 Power
Therefore I added index 0 - index 2 to the flow of every stream (maybe there are other ways but I found this to be quite obviously and helpful) - success.
But though the index is part of every single message then, I can neither sort it before joining. Hope someone has an easy suggestion for this.
With kind regards
Ralf

2

Hi and welcome to the forum.

You can fix the order after the join using JSONata in a change node, or Javascript in a function node.
example

[{"id":"693609f1bb222e12","type":"mqtt in","z":"b3968456737a18dc","name":"","topic":"BMS_Watchdog/Data/+","qos":"2","datatype":"json","broker":"10e78a89.5b4fd5","nl":false,"rap":false,"inputs":0,"x":120,"y":120,"wires":[["38c15124ef4e92fb"]]},{"id":"38c15124ef4e92fb","type":"change","z":"b3968456737a18dc","name":"","rules":[{"t":"set","p":"topic","pt":"msg","to":"$split($$.topic, \"/\")[2]","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":400,"y":120,"wires":[["ccfb8a0c9253142f"]]},{"id":"8aec70a3dbe73f24","type":"inject","z":"b3968456737a18dc","name":"volt","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"BMS_Watchdog/Data/Battery_Voltage","payload":"48","payloadType":"num","x":370,"y":220,"wires":[["38c15124ef4e92fb"]]},{"id":"0da9f0fcd239c30f","type":"inject","z":"b3968456737a18dc","name":"charge","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"BMS_Watchdog/Data/Charge_Current","payload":"20","payloadType":"num","x":370,"y":260,"wires":[["38c15124ef4e92fb"]]},{"id":"e4a5f3515d1cbe4a","type":"inject","z":"b3968456737a18dc","name":"power","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"BMS_Watchdog/Data/Battery_Power","payload":"90","payloadType":"num","x":370,"y":300,"wires":[["38c15124ef4e92fb"]]},{"id":"ccfb8a0c9253142f","type":"join","z":"b3968456737a18dc","name":"","mode":"custom","build":"object","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","useparts":false,"accumulate":false,"timeout":"","count":"3","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"num","reduceFixup":"","x":570,"y":120,"wires":[["354f3f7a446c30bd"]]},{"id":"354f3f7a446c30bd","type":"change","z":"b3968456737a18dc","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"{\t   \"Battery_Voltage\":$$.payload.Battery_Voltage,\t   \"Charge_Current\": $$.payload.Charge_Current,\t   \"Battery_Power\":$$.payload.Battery_Power\t}","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":720,"y":120,"wires":[["4e9be2c79756f9c2"]]},{"id":"4e9be2c79756f9c2","type":"debug","z":"b3968456737a18dc","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":890,"y":120,"wires":[]},{"id":"10e78a89.5b4fd5","type":"mqtt-broker","name":"","broker":"localhost","port":"1883","clientid":"","autoConnect":true,"usetls":false,"protocolVersion":4,"keepalive":"60","cleansession":true,"autoUnsubscribe":true,"birthTopic":"","birthQos":"0","birthPayload":"","birthMsg":{},"closeTopic":"","closeQos":"0","closePayload":"","closeMsg":{},"willTopic":"","willQos":"0","willPayload":"","willMsg":{},"userProps":"","sessionExpiry":""}]

You can also use one mqtt node and extract the joint name from the topic.

3

I have dabbled with Influx but don't use it any more.

Why does it matter what order the values are in - can't you write an insert query which depends on the key not position to identify measurements? (as you can with eg MySQL)

4

Successfully imported your script to my workspace for checking.
As I'm really new with all this, it'll take a while to check your suggestions.
Thanks for replying.

5

"I have dabbled with Influx but don't use it any more." - I'm rookie so far, maybe after 1 or 2 years I'll also leave InfluxDB but now now.
" can't you write an insert query which depends on the key not position to identify measurements?"
Old style of thinking that everything has to be in the right order... Of course if I create dashboards I'll use key words instead of positions... just old habits.

6

It is best not to do it that way. Just send each process value to its own Measurement in the database, don't go to the effort of joining the messages into one record. Influxdb is optimised to be used that way, it isn't like an SQL record based database. You will end up with much simpler flows, and simple flows means fewer obscure bugs and easier maintenance.

So, from the flow you posted, have a measurement for the charge current, another for battery power, and another for battery voltage.

7

Hi Colin,
thanks for the clear words.
The thing behind is: Voltage, Current and Power come from the same message on ESP32.
There were picked up exactly the same time.
The ESP32 script splits the whole set of measurements into single measurements and send it separatley. Consequently if following your suggestion every single measurement will have its own timestamp. With this, it is much more difficult to calculate with single measurements. It is easy for me to modify the ESP32 script to send everything I need in one message as I´m quite skilled with arduino stuff. The challenge was to join it together again in node red - seemed to be a good exercise to learn this new language.
But I got your point and will modify ESP message

8

Definitely that is the way to do it. Send it as a JSON string representation of an object with the three key/value pairs. Then you can configure the MQTT node to parse the JSON to a javascript object which you can send directly to Influxdb.