Modbus to mySQL flow suggestions

Hello everyone,

i’m trying to log some values from modbus devices to the mySQL node. the flow is not very clear to me as functionality and setup, so i’m posting here what i’m trying to do and asking how is possible to achieve something like this.

I’m familiar with the modbus read and buffer parser nodes individually, but since are coming from different devices, here question 1 arises.

  1. how is recommended to merge the outputs of 3 different buffer parsers to merge the values before going to database? i’m using key/value pairs as output, but it can be any type if more convenient. Is there any other way to merge all the values from 3 devices?
  2. on the examples for mysql, i saw using template node and mustache syntax for creating the query (topic and payload , or topic and get the payload from previous node). Assuming i have all my values from the previous step in key/value pairs, where each key coming from buffer parsers are the column name, and each value is the record value for each column, how this can be done dynamically in the template for a successful sql insert into table ?

This procedure i got it by checking out the documentation and recommendations. i don’t know if is the easiest way or the suggested way (i guess it is that’s why i have it as starting point), but i’m not fixated on that, if another method is easier i’m willing to change methodology and flow.

Thanks for any suggestions.

[
    {
        "id": "f20a075846751b87",
        "type": "modbus-read",
        "z": "4e7505dfcf1c08de",
        "name": "modbus read device 1",
        "topic": "",
        "showStatusActivities": false,
        "logIOActivities": false,
        "showErrors": false,
        "showWarnings": true,
        "unitid": "",
        "dataType": "",
        "adr": "",
        "quantity": "",
        "rate": "",
        "rateUnit": "",
        "delayOnStart": false,
        "startDelayTime": "",
        "server": "",
        "useIOFile": false,
        "ioFile": "",
        "useIOForPayload": false,
        "emptyMsgOnFail": false,
        "x": 300,
        "y": 380,
        "wires": [
            [
                "4d06079ace179c40"
            ],
            []
        ]
    },
    {
        "id": "7811dd6a8dce739f",
        "type": "modbus-read",
        "z": "4e7505dfcf1c08de",
        "name": "modbus read device 2",
        "topic": "",
        "showStatusActivities": false,
        "logIOActivities": false,
        "showErrors": false,
        "showWarnings": true,
        "unitid": "",
        "dataType": "",
        "adr": "",
        "quantity": "",
        "rate": "",
        "rateUnit": "",
        "delayOnStart": false,
        "startDelayTime": "",
        "server": "",
        "useIOFile": false,
        "ioFile": "",
        "useIOForPayload": false,
        "emptyMsgOnFail": false,
        "x": 300,
        "y": 440,
        "wires": [
            [
                "0572ea4d6dc4c8d6"
            ],
            []
        ]
    },
    {
        "id": "066dcff404d48e58",
        "type": "modbus-read",
        "z": "4e7505dfcf1c08de",
        "name": "modbus read device 3",
        "topic": "",
        "showStatusActivities": false,
        "logIOActivities": false,
        "showErrors": false,
        "showWarnings": true,
        "unitid": "",
        "dataType": "",
        "adr": "",
        "quantity": "",
        "rate": "",
        "rateUnit": "",
        "delayOnStart": false,
        "startDelayTime": "",
        "server": "",
        "useIOFile": false,
        "ioFile": "",
        "useIOForPayload": false,
        "emptyMsgOnFail": false,
        "x": 300,
        "y": 500,
        "wires": [
            [
                "ea0e4736537ce6cd"
            ],
            []
        ]
    },
    {
        "id": "4d06079ace179c40",
        "type": "buffer-parser",
        "z": "4e7505dfcf1c08de",
        "name": "",
        "data": "payload",
        "dataType": "msg",
        "specification": "spec",
        "specificationType": "ui",
        "items": [
            {
                "type": "int16be",
                "name": "bom",
                "offset": 4,
                "length": 1,
                "offsetbit": 0,
                "scale": "0.1",
                "mask": ""
            },
            {
                "type": "int16be",
                "name": "plane",
                "offset": 24,
                "length": 1,
                "offsetbit": 0,
                "scale": "0.1",
                "mask": ""
            },
            {
                "type": "int16be",
                "name": "noneed",
                "offset": 26,
                "length": 1,
                "offsetbit": 0,
                "scale": "0.1",
                "mask": ""
            },
            {
                "type": "int16be",
                "name": "ambient",
                "offset": 56,
                "length": 1,
                "offsetbit": 0,
                "scale": "0.1",
                "mask": ""
            },
            {
                "type": "int16be",
                "name": "horizontal",
                "offset": 58,
                "length": 1,
                "offsetbit": 0,
                "scale": "0.1",
                "mask": ""
            },
            {
                "type": "int16be",
                "name": "item4",
                "offset": 76,
                "length": 1,
                "offsetbit": 0,
                "scale": "0.1",
                "mask": ""
            },
            {
                "type": "int16be",
                "name": "wind",
                "offset": 200,
                "length": 1,
                "offsetbit": 0,
                "scale": "0.1",
                "mask": ""
            }
        ],
        "swap1": "",
        "swap2": "",
        "swap3": "",
        "swap1Type": "swap",
        "swap2Type": "swap",
        "swap3Type": "swap",
        "msgProperty": "payload",
        "msgPropertyType": "str",
        "resultType": "keyvalue",
        "resultTypeType": "return",
        "multipleResult": false,
        "fanOutMultipleResult": false,
        "setTopic": false,
        "outputs": 1,
        "x": 530,
        "y": 380,
        "wires": [
            [
                "f60288d0239696bf"
            ]
        ]
    },
    {
        "id": "0572ea4d6dc4c8d6",
        "type": "buffer-parser",
        "z": "4e7505dfcf1c08de",
        "name": "",
        "data": "payload",
        "dataType": "msg",
        "specification": "spec",
        "specificationType": "ui",
        "items": [
            {
                "type": "int16be",
                "name": "bom",
                "offset": 4,
                "length": 1,
                "offsetbit": 0,
                "scale": "0.1",
                "mask": ""
            },
            {
                "type": "int16be",
                "name": "plane",
                "offset": 24,
                "length": 1,
                "offsetbit": 0,
                "scale": "0.1",
                "mask": ""
            },
            {
                "type": "int16be",
                "name": "noneed",
                "offset": 26,
                "length": 1,
                "offsetbit": 0,
                "scale": "0.1",
                "mask": ""
            },
            {
                "type": "int16be",
                "name": "ambient",
                "offset": 56,
                "length": 1,
                "offsetbit": 0,
                "scale": "0.1",
                "mask": ""
            },
            {
                "type": "int16be",
                "name": "horizontal",
                "offset": 58,
                "length": 1,
                "offsetbit": 0,
                "scale": "0.1",
                "mask": ""
            },
            {
                "type": "int16be",
                "name": "item4",
                "offset": 76,
                "length": 1,
                "offsetbit": 0,
                "scale": "0.1",
                "mask": ""
            },
            {
                "type": "int16be",
                "name": "wind",
                "offset": 200,
                "length": 1,
                "offsetbit": 0,
                "scale": "0.1",
                "mask": ""
            }
        ],
        "swap1": "",
        "swap2": "",
        "swap3": "",
        "swap1Type": "swap",
        "swap2Type": "swap",
        "swap3Type": "swap",
        "msgProperty": "payload",
        "msgPropertyType": "str",
        "resultType": "keyvalue",
        "resultTypeType": "return",
        "multipleResult": false,
        "fanOutMultipleResult": false,
        "setTopic": false,
        "outputs": 1,
        "x": 530,
        "y": 440,
        "wires": [
            [
                "f60288d0239696bf"
            ]
        ]
    },
    {
        "id": "ea0e4736537ce6cd",
        "type": "buffer-parser",
        "z": "4e7505dfcf1c08de",
        "name": "",
        "data": "payload",
        "dataType": "msg",
        "specification": "spec",
        "specificationType": "ui",
        "items": [
            {
                "type": "int16be",
                "name": "bom",
                "offset": 4,
                "length": 1,
                "offsetbit": 0,
                "scale": "0.1",
                "mask": ""
            },
            {
                "type": "int16be",
                "name": "plane",
                "offset": 24,
                "length": 1,
                "offsetbit": 0,
                "scale": "0.1",
                "mask": ""
            },
            {
                "type": "int16be",
                "name": "noneed",
                "offset": 26,
                "length": 1,
                "offsetbit": 0,
                "scale": "0.1",
                "mask": ""
            },
            {
                "type": "int16be",
                "name": "ambient",
                "offset": 56,
                "length": 1,
                "offsetbit": 0,
                "scale": "0.1",
                "mask": ""
            },
            {
                "type": "int16be",
                "name": "horizontal",
                "offset": 58,
                "length": 1,
                "offsetbit": 0,
                "scale": "0.1",
                "mask": ""
            },
            {
                "type": "int16be",
                "name": "item4",
                "offset": 76,
                "length": 1,
                "offsetbit": 0,
                "scale": "0.1",
                "mask": ""
            },
            {
                "type": "int16be",
                "name": "wind",
                "offset": 200,
                "length": 1,
                "offsetbit": 0,
                "scale": "0.1",
                "mask": ""
            }
        ],
        "swap1": "",
        "swap2": "",
        "swap3": "",
        "swap1Type": "swap",
        "swap2Type": "swap",
        "swap3Type": "swap",
        "msgProperty": "payload",
        "msgPropertyType": "str",
        "resultType": "keyvalue",
        "resultTypeType": "return",
        "multipleResult": false,
        "fanOutMultipleResult": false,
        "setTopic": false,
        "outputs": 1,
        "x": 530,
        "y": 500,
        "wires": [
            [
                "f60288d0239696bf"
            ]
        ]
    },
    {
        "id": "f60288d0239696bf",
        "type": "join",
        "z": "4e7505dfcf1c08de",
        "name": "",
        "mode": "auto",
        "build": "object",
        "property": "payload",
        "propertyType": "msg",
        "key": "topic",
        "joiner": "\\n",
        "joinerType": "str",
        "useparts": false,
        "accumulate": "false",
        "timeout": "",
        "count": "",
        "reduceRight": false,
        "x": 750,
        "y": 440,
        "wires": [
            [
                "a0a3268dedeb52d8"
            ]
        ]
    },
    {
        "id": "a0a3268dedeb52d8",
        "type": "template",
        "z": "4e7505dfcf1c08de",
        "name": "",
        "field": "payload",
        "fieldType": "msg",
        "format": "handlebars",
        "syntax": "mustache",
        "template": "This is the payload: {{payload}} !",
        "output": "str",
        "x": 900,
        "y": 440,
        "wires": [
            [
                "b0b1a401ed52004f"
            ]
        ]
    },
    {
        "id": "b0b1a401ed52004f",
        "type": "mysql",
        "z": "4e7505dfcf1c08de",
        "name": "",
        "x": 1070,
        "y": 440,
        "wires": [
            [
                "096f763aa136ee75"
            ]
        ]
    },
    {
        "id": "096f763aa136ee75",
        "type": "debug",
        "z": "4e7505dfcf1c08de",
        "name": "debug sql",
        "active": false,
        "tosidebar": true,
        "console": false,
        "tostatus": true,
        "complete": "payload",
        "targetType": "msg",
        "statusVal": "payload",
        "statusType": "auto",
        "x": 1220,
        "y": 440,
        "wires": []
    }
]

You don't seem to have shared your SQL or the structure of your database table.

However, why do you want to combine data from 3 different devices into a single record? Why not store each one individually?

Hello my friend.

the table is just 15 columns, the first is timestamp, 14 are real / float values.

To complete one correct record of the values needed in the database, we have to modbus poll from 3 different devices from the modbus field on the same timestamp. So a merge is needed.

Use the Flex Getter node, so that you manually trigger the reads, and read them in series not in parallel. So read from the first device, feed it through the parser, and save the results in, for example, msg.device1, then use that message to trigger the second getter node, feed the results through the parser, and save in msg.device2 (msg.device1 should still be in the message), then on into the getter node for device3 and the parser. At the end you will have all three sets of data in one message, in msg.device1, msg.device2 and msg.payload. Then you can build the query as required.

For point two use a Prepared query with named parameters as described in the docs, then the query is fixed. You can use a Change node to build the required message.

Thanks for your comment.
what is the input of the modbus flex getter ? i assume is the modbus parameters needed. can this be done with template ? i’m not familiar with javascript.

The node-red-contrib-modbus module has built in help for each of its nodes (see the RH sidebar) and lots of built in example flows that you can access via CTRL-I -> examples -> node-red-contrib-modbus

Hello Mr Steve,

i used a template node with output parsed json and feed the flex getter. this point it works !

Then i use a buffer parser to process the modbus registers as usual and get key/values json object to add variable names and then set output property on msg.device1 for identification.
When do that on cascaded devices like the previous suggestion of Colin, it seems that flex getter node not passing the previous messages, not allowing for aggregation.

on the output of each buffer parser, i have msg.device1 , msg.device2 normally… but when the message passes to another flex getter is loosing the previous message with msg.device1.

(I have checked this, before each flex getter the previous message exists , even after the template node).

As a result, on the last buffer parser i have only the msg.device4.

How can i aggregate several modbus polls from different devices like this example ?
The reason is that i need to have a complete record of all the values (about 15) so i can do mysql insert into table …. all the record with all the values.

Please see the topology below and thanks for any suggestions.


[
    {
        "id": "b87a6f58b4d9da38",
        "type": "inject",
        "z": "4e7505dfcf1c08de",
        "name": "",
        "props": [
            {
                "p": "payload"
            },
            {
                "p": "topic",
                "vt": "str"
            }
        ],
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "",
        "payload": "",
        "payloadType": "date",
        "x": 160,
        "y": 520,
        "wires": [
            [
                "52a73644e520116e"
            ]
        ]
    },
    {
        "id": "68fefb0959380fba",
        "type": "buffer-parser",
        "z": "4e7505dfcf1c08de",
        "name": "",
        "data": "payload",
        "dataType": "msg",
        "specification": "spec",
        "specificationType": "ui",
        "items": [
            {
                "type": "int16be",
                "name": "bom",
                "offset": 0,
                "length": 1,
                "offsetbit": 0,
                "scale": "0.1",
                "mask": ""
            }
        ],
        "swap1": "",
        "swap2": "",
        "swap3": "",
        "swap1Type": "swap",
        "swap2Type": "swap",
        "swap3Type": "swap",
        "msgProperty": "device1",
        "msgPropertyType": "str",
        "resultType": "keyvalue",
        "resultTypeType": "return",
        "multipleResult": false,
        "fanOutMultipleResult": false,
        "setTopic": false,
        "outputs": 1,
        "x": 790,
        "y": 520,
        "wires": [
            [
                "5d3f637df3afd34d",
                "e7cd83a7bf6dae33"
            ]
        ]
    },
    {
        "id": "5d3f637df3afd34d",
        "type": "debug",
        "z": "4e7505dfcf1c08de",
        "name": "debug 23",
        "active": false,
        "tosidebar": true,
        "console": false,
        "tostatus": true,
        "complete": "true",
        "targetType": "full",
        "statusVal": "payload",
        "statusType": "auto",
        "x": 1040,
        "y": 520,
        "wires": []
    },
    {
        "id": "b65bd4a099fb7463",
        "type": "modbus-flex-getter",
        "z": "4e7505dfcf1c08de",
        "name": "",
        "showStatusActivities": false,
        "showErrors": false,
        "showWarnings": true,
        "logIOActivities": false,
        "server": "9502213b25a893a7",
        "useIOFile": false,
        "ioFile": "",
        "useIOForPayload": false,
        "emptyMsgOnFail": false,
        "keepMsgProperties": false,
        "delayOnStart": false,
        "startDelayTime": "",
        "x": 550,
        "y": 520,
        "wires": [
            [
                "68fefb0959380fba",
                "19a5bb3d44791f29"
            ],
            []
        ]
    },
    {
        "id": "19a5bb3d44791f29",
        "type": "debug",
        "z": "4e7505dfcf1c08de",
        "name": "debug 24",
        "active": false,
        "tosidebar": true,
        "console": false,
        "tostatus": true,
        "complete": "payload",
        "targetType": "msg",
        "statusVal": "payload",
        "statusType": "auto",
        "x": 780,
        "y": 420,
        "wires": []
    },
    {
        "id": "e9162f836b802971",
        "type": "buffer-parser",
        "z": "4e7505dfcf1c08de",
        "name": "",
        "data": "payload",
        "dataType": "msg",
        "specification": "spec",
        "specificationType": "ui",
        "items": [
            {
                "type": "int16be",
                "name": "bom2",
                "offset": 0,
                "length": 1,
                "offsetbit": 0,
                "scale": "0.1",
                "mask": ""
            }
        ],
        "swap1": "",
        "swap2": "",
        "swap3": "",
        "swap1Type": "swap",
        "swap2Type": "swap",
        "swap3Type": "swap",
        "msgProperty": "device2",
        "msgPropertyType": "str",
        "resultType": "keyvalue",
        "resultTypeType": "return",
        "multipleResult": false,
        "fanOutMultipleResult": false,
        "setTopic": false,
        "outputs": 1,
        "x": 790,
        "y": 620,
        "wires": [
            [
                "8fc77d8bb986dec9",
                "5408fadabbd81bd8"
            ]
        ]
    },
    {
        "id": "8fc77d8bb986dec9",
        "type": "debug",
        "z": "4e7505dfcf1c08de",
        "name": "debug 25",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": true,
        "complete": "true",
        "targetType": "full",
        "statusVal": "payload",
        "statusType": "auto",
        "x": 1060,
        "y": 620,
        "wires": []
    },
    {
        "id": "52a73644e520116e",
        "type": "template",
        "z": "4e7505dfcf1c08de",
        "name": "",
        "field": "payload",
        "fieldType": "msg",
        "format": "handlebars",
        "syntax": "mustache",
        "template": "{\n\"fc\":3,\n\"unitid\":100,\n\"address\":51003,\n\"quantity\":2\n}",
        "output": "json",
        "x": 360,
        "y": 520,
        "wires": [
            [
                "b65bd4a099fb7463"
            ]
        ]
    },
    {
        "id": "e7cd83a7bf6dae33",
        "type": "template",
        "z": "4e7505dfcf1c08de",
        "name": "",
        "field": "payload",
        "fieldType": "msg",
        "format": "handlebars",
        "syntax": "mustache",
        "template": "{\n\n\n\"fc\":3,\n\"unitid\":100,\n\"address\":51003,\n\"quantity\":8\n\n}",
        "output": "json",
        "x": 360,
        "y": 620,
        "wires": [
            [
                "b8eb98aed89ea8bd"
            ]
        ]
    },
    {
        "id": "b8eb98aed89ea8bd",
        "type": "modbus-flex-getter",
        "z": "4e7505dfcf1c08de",
        "name": "",
        "showStatusActivities": false,
        "showErrors": false,
        "showWarnings": true,
        "logIOActivities": false,
        "server": "9502213b25a893a7",
        "useIOFile": false,
        "ioFile": "",
        "useIOForPayload": false,
        "emptyMsgOnFail": false,
        "keepMsgProperties": false,
        "delayOnStart": false,
        "startDelayTime": "",
        "x": 550,
        "y": 620,
        "wires": [
            [
                "e9162f836b802971"
            ],
            []
        ]
    },
    {
        "id": "59f45a66a6919264",
        "type": "buffer-parser",
        "z": "4e7505dfcf1c08de",
        "name": "",
        "data": "payload",
        "dataType": "msg",
        "specification": "spec",
        "specificationType": "ui",
        "items": [
            {
                "type": "int16be",
                "name": "bom3",
                "offset": 0,
                "length": 1,
                "offsetbit": 0,
                "scale": "0.1",
                "mask": ""
            }
        ],
        "swap1": "",
        "swap2": "",
        "swap3": "",
        "swap1Type": "swap",
        "swap2Type": "swap",
        "swap3Type": "swap",
        "msgProperty": "device3",
        "msgPropertyType": "str",
        "resultType": "keyvalue",
        "resultTypeType": "return",
        "multipleResult": false,
        "fanOutMultipleResult": false,
        "setTopic": false,
        "outputs": 1,
        "x": 790,
        "y": 740,
        "wires": [
            [
                "6425ef352c2dcc9e",
                "dc3d9d0c9bb2096d"
            ]
        ]
    },
    {
        "id": "6425ef352c2dcc9e",
        "type": "debug",
        "z": "4e7505dfcf1c08de",
        "name": "debug 21",
        "active": false,
        "tosidebar": true,
        "console": false,
        "tostatus": true,
        "complete": "true",
        "targetType": "full",
        "statusVal": "payload",
        "statusType": "auto",
        "x": 1060,
        "y": 740,
        "wires": []
    },
    {
        "id": "5408fadabbd81bd8",
        "type": "template",
        "z": "4e7505dfcf1c08de",
        "name": "",
        "field": "payload",
        "fieldType": "msg",
        "format": "handlebars",
        "syntax": "mustache",
        "template": "{\n\n\n\"fc\":3,\n\"unitid\":100,\n\"address\":51003,\n\"quantity\":4\n\n}",
        "output": "json",
        "x": 360,
        "y": 740,
        "wires": [
            [
                "aca64b662f2b3ad5"
            ]
        ]
    },
    {
        "id": "aca64b662f2b3ad5",
        "type": "modbus-flex-getter",
        "z": "4e7505dfcf1c08de",
        "name": "",
        "showStatusActivities": false,
        "showErrors": false,
        "showWarnings": true,
        "logIOActivities": false,
        "server": "9502213b25a893a7",
        "useIOFile": false,
        "ioFile": "",
        "useIOForPayload": false,
        "emptyMsgOnFail": false,
        "keepMsgProperties": false,
        "delayOnStart": false,
        "startDelayTime": "",
        "x": 550,
        "y": 740,
        "wires": [
            [
                "59f45a66a6919264"
            ],
            []
        ]
    },
    {
        "id": "2d482254678a8509",
        "type": "buffer-parser",
        "z": "4e7505dfcf1c08de",
        "name": "",
        "data": "payload",
        "dataType": "msg",
        "specification": "spec",
        "specificationType": "ui",
        "items": [
            {
                "type": "int16be",
                "name": "bom4",
                "offset": 0,
                "length": 1,
                "offsetbit": 0,
                "scale": "0.1",
                "mask": ""
            }
        ],
        "swap1": "",
        "swap2": "",
        "swap3": "",
        "swap1Type": "swap",
        "swap2Type": "swap",
        "swap3Type": "swap",
        "msgProperty": "device4",
        "msgPropertyType": "str",
        "resultType": "keyvalue",
        "resultTypeType": "return",
        "multipleResult": false,
        "fanOutMultipleResult": false,
        "setTopic": false,
        "outputs": 1,
        "x": 790,
        "y": 840,
        "wires": [
            [
                "25348e5af2c5da62"
            ]
        ]
    },
    {
        "id": "25348e5af2c5da62",
        "type": "debug",
        "z": "4e7505dfcf1c08de",
        "name": "debug 26",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": true,
        "complete": "true",
        "targetType": "full",
        "statusVal": "payload",
        "statusType": "auto",
        "x": 1060,
        "y": 840,
        "wires": []
    },
    {
        "id": "dc3d9d0c9bb2096d",
        "type": "template",
        "z": "4e7505dfcf1c08de",
        "name": "",
        "field": "payload",
        "fieldType": "msg",
        "format": "handlebars",
        "syntax": "mustache",
        "template": "{\n\n\n\"fc\":3,\n\"unitid\":100,\n\"address\":51003,\n\"quantity\":8\n\n}",
        "output": "json",
        "x": 360,
        "y": 840,
        "wires": [
            [
                "fced70b955862b8a"
            ]
        ]
    },
    {
        "id": "fced70b955862b8a",
        "type": "modbus-flex-getter",
        "z": "4e7505dfcf1c08de",
        "name": "",
        "showStatusActivities": false,
        "showErrors": false,
        "showWarnings": true,
        "logIOActivities": false,
        "server": "9502213b25a893a7",
        "useIOFile": false,
        "ioFile": "",
        "useIOForPayload": false,
        "emptyMsgOnFail": false,
        "keepMsgProperties": false,
        "delayOnStart": false,
        "startDelayTime": "",
        "x": 550,
        "y": 840,
        "wires": [
            [
                "2d482254678a8509"
            ],
            []
        ]
    },
    {
        "id": "9502213b25a893a7",
        "type": "modbus-client",
        "name": "3hats1,3 smartlogger",
        "clienttype": "tcp",
        "bufferCommands": true,
        "stateLogEnabled": false,
        "queueLogEnabled": false,
        "failureLogEnabled": true,
        "tcpHost": "192.168.50.22",
        "tcpPort": 502,
        "tcpType": "DEFAULT",
        "serialPort": "/dev/ttyUSB",
        "serialType": "RTU-BUFFERD",
        "serialBaudrate": 9600,
        "serialDatabits": 8,
        "serialStopbits": 1,
        "serialParity": "none",
        "serialConnectionDelay": 100,
        "serialAsciiResponseStartDelimiter": "0x3A",
        "unit_id": 100,
        "commandDelay": 1,
        "clientTimeout": 1000,
        "reconnectOnTimeout": true,
        "reconnectTimeout": 2000,
        "parallelUnitIdsAllowed": true,
        "showErrors": false,
        "showWarnings": true,
        "showLogs": true
    }
]

Check the flex getter edit dialog - IIRC it has an option to "keep existing msg props" (I forget the name)

1 Like

Usually you would use a Change node for this. Why do you need to use a template?

thank you very much, this was the issue. now i have all devices.

what would be the recommended to merge those ?? change node ?

is just for easier reading and typing. with template i have it like this:

{  
"fc":3, 
"unitid":100, 
"address":51003, 
"quantity":2  
}

with a change node everything is populated in a single text box like below:

Yes, or a function node.

What I dont understand is why do you always access the same modbus server and same address for these values? If all values are from the same modbus server/unit ID, then you could do this all in one request and one buffer parser.

yes, is proof of concept now using only the first device. in reality is 4 different devices as the prototype here, and unfortunately with different encoding methods. (the usual modbus headache).

How it sounds to you to merge all the devices json objects with a change node and JSONata expression ?

It depends what you want to do with the final data and what else (additional to bom) will be in the final data.

the end goal is to aggregate all the values to a single key/value object so we can inject to mysql node with topic insert into table…
now the messages are aggregated and i’m looking how to merge all the incoming data to a single object of keys/values because in mysql node need to be all together to complete one database record.

the merging needs to be :

{
bom: value1
bom2: value2
bom3: value3
bom4: value4
}

so i’m looking for a jsonata expression in a change node to do that.

in which case I would set the values in to a sub property like msg.data and use a common name for the bom values (e.g value), then use a simple function that loops over each key and gathers the .value of each sub property in msg.data

[{"id":"b87a6f58b4d9da38","type":"inject","z":"9a5fb6c794f8556d","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":500,"y":300,"wires":[["52a73644e520116e"]]},{"id":"52a73644e520116e","type":"template","z":"9a5fb6c794f8556d","name":"","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"{\n\"fc\":3,\n\"unitid\":100,\n\"address\":51003,\n\"quantity\":2\n}","output":"json","x":500,"y":380,"wires":[["eecc98279b116bb8"]]},{"id":"eecc98279b116bb8","type":"function","z":"9a5fb6c794f8556d","name":"fake data","func":"msg.payload = [3609]\nreturn msg;","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[],"x":680,"y":380,"wires":[["68fefb0959380fba"]]},{"id":"68fefb0959380fba","type":"buffer-parser","z":"9a5fb6c794f8556d","name":"","data":"payload","dataType":"msg","specification":"spec","specificationType":"ui","items":[{"type":"int16be","name":"value","offset":0,"length":1,"offsetbit":0,"scale":"0.1","mask":""}],"swap1":"","swap2":"","swap3":"","swap1Type":"swap","swap2Type":"swap","swap3Type":"swap","msgProperty":"data.bom1","msgPropertyType":"str","resultType":"keyvalue","resultTypeType":"return","multipleResult":false,"fanOutMultipleResult":false,"setTopic":false,"outputs":1,"x":860,"y":380,"wires":[["e7cd83a7bf6dae33"]]},{"id":"e7cd83a7bf6dae33","type":"template","z":"9a5fb6c794f8556d","name":"","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"{\n\n\n\"fc\":3,\n\"unitid\":100,\n\"address\":51003,\n\"quantity\":8\n\n}","output":"json","x":500,"y":480,"wires":[["bea4b7b6b486d1c9"]]},{"id":"bea4b7b6b486d1c9","type":"function","z":"9a5fb6c794f8556d","name":"fake data","func":"msg.payload = [3609]\nreturn msg;","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[],"x":680,"y":480,"wires":[["e9162f836b802971"]]},{"id":"e9162f836b802971","type":"buffer-parser","z":"9a5fb6c794f8556d","name":"","data":"payload","dataType":"msg","specification":"spec","specificationType":"ui","items":[{"type":"int16be","name":"value","offset":0,"length":1,"offsetbit":0,"scale":"0.1","mask":""}],"swap1":"","swap2":"","swap3":"","swap1Type":"swap","swap2Type":"swap","swap3Type":"swap","msgProperty":"data.bom2","msgPropertyType":"str","resultType":"keyvalue","resultTypeType":"return","multipleResult":false,"fanOutMultipleResult":false,"setTopic":false,"outputs":1,"x":860,"y":480,"wires":[["5408fadabbd81bd8"]]},{"id":"5408fadabbd81bd8","type":"template","z":"9a5fb6c794f8556d","name":"","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"{\n\n\n\"fc\":3,\n\"unitid\":100,\n\"address\":51003,\n\"quantity\":4\n\n}","output":"json","x":500,"y":580,"wires":[["6710c32c5ee43105"]]},{"id":"6710c32c5ee43105","type":"function","z":"9a5fb6c794f8556d","name":"fake data","func":"msg.payload = [3609]\nreturn msg;","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[],"x":680,"y":580,"wires":[["59f45a66a6919264"]]},{"id":"59f45a66a6919264","type":"buffer-parser","z":"9a5fb6c794f8556d","name":"","data":"payload","dataType":"msg","specification":"spec","specificationType":"ui","items":[{"type":"int16be","name":"value","offset":0,"length":1,"offsetbit":0,"scale":"0.1","mask":""}],"swap1":"","swap2":"","swap3":"","swap1Type":"swap","swap2Type":"swap","swap3Type":"swap","msgProperty":"data.bom3","msgPropertyType":"str","resultType":"keyvalue","resultTypeType":"return","multipleResult":false,"fanOutMultipleResult":false,"setTopic":false,"outputs":1,"x":860,"y":580,"wires":[["dc3d9d0c9bb2096d"]]},{"id":"dc3d9d0c9bb2096d","type":"template","z":"9a5fb6c794f8556d","name":"","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"{\n\n\n\"fc\":3,\n\"unitid\":100,\n\"address\":51003,\n\"quantity\":8\n\n}","output":"json","x":500,"y":680,"wires":[["23e89f40f65629e7"]]},{"id":"23e89f40f65629e7","type":"function","z":"9a5fb6c794f8556d","name":"fake data","func":"msg.payload = [3609]\nreturn msg;","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[],"x":680,"y":680,"wires":[["2d482254678a8509"]]},{"id":"2d482254678a8509","type":"buffer-parser","z":"9a5fb6c794f8556d","name":"","data":"payload","dataType":"msg","specification":"spec","specificationType":"ui","items":[{"type":"int16be","name":"value","offset":0,"length":1,"offsetbit":0,"scale":"0.1","mask":""}],"swap1":"","swap2":"","swap3":"","swap1Type":"swap","swap2Type":"swap","swap3Type":"swap","msgProperty":"data.bom4","msgPropertyType":"str","resultType":"keyvalue","resultTypeType":"return","multipleResult":false,"fanOutMultipleResult":false,"setTopic":false,"outputs":1,"x":860,"y":680,"wires":[["25348e5af2c5da62","81b975a65c889dba"]]},{"id":"25348e5af2c5da62","type":"debug","z":"9a5fb6c794f8556d","name":"msg.data","active":true,"tosidebar":true,"console":false,"tostatus":true,"complete":"data","targetType":"msg","statusVal":"payload","statusType":"auto","x":1060,"y":680,"wires":[]},{"id":"81b975a65c889dba","type":"function","z":"9a5fb6c794f8556d","name":"combine data into 1 payload","func":"const result = {}\nfor (const prop in msg.data) {\n    result[prop] = msg.data[prop].value\n}\nmsg.payload = result\nreturn msg;","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[],"x":820,"y":820,"wires":[["80f375926dd54073"]]},{"id":"80f375926dd54073","type":"debug","z":"9a5fb6c794f8556d","name":"msg.payload","active":true,"tosidebar":true,"console":false,"tostatus":true,"complete":"payload","targetType":"msg","statusVal":"payload","statusType":"auto","x":1070,"y":820,"wires":[]}]

Thank you Mr. Steve,

in reality the structure would be 3 or 4 modbus devices with 4 or 5 values as output of each buffer parser.

the function probably will not work as it is, but is charming how it executes on each item with loop.

I just understand how to use all the messages in a template.. it’s a manual code to adjust, but is giving the opportunity to remap and / or rearrange values from the message to the output key values that will be used as the database columns. something like below:


{

"value1" : {{device1.bom}},
"value2" : {{device2.bom2}},
"value3" : {{device3.bom3}},
"value4" : {{device4.bom4}},
"value5" : {{device1.temp}},
"value6" : {{device2.pac}},
"value7" : {{device3.sac}},
"value8" : {{device4.qac}},
"value9" : {{device1.ambient}},
"value10" : {{device2.pout}},
"value11" : {{device3.sout}},
"value12" : {{device4.qout}}

}

is a change node any helpful to do something like this ?

Which is why I said

Again it depends what you want to do with it and how you doing it.

If you were using a SQL node that supports parameters (which btw, I strongly recommend to avoid sqli hacks) then you probably don't have to do anything at all. You can just access the values from wherever they exist in the message.

I'd rather not waste time showing you a better / dynamic / maintenance-free way of arranging the data unless you are more specific with how the data will be used.