Afternoon everyone,
I have a flow that currently works but I think it could be prone to message errors and what I've attempted to do is build this into single function node that loops the data instead. So the end goal is to retrieve my sensors from my MariaDB table to construct a SparkPlug B Birth message. My question isn't around the SparkPlug B message or structure itself but trying to fine a more efficient way to split/join my original message into the final message. I believe this can be done with a loop function node. I've done something similar before but only when I'm trying to combine payloads. In this case I have multiple object arrays that I need to combine as a metric called "Definition" and then also build a combined payload, all of which will be a single message. The flow I've attached has both the working Split / Join / Join process that works fine and a function node where I'm failing to accomplish the same steps. It could be the community comes back with my split / join / join is the best approach and I'd be ok with that.
Inside my test table I have 3 seniors as shown here:
The inject node sends a query to the table to get the list of sensors and it responds with this format:
I then split the message into the separate objects where I run those through 2 different joins. The first one combines the Name/Type into the "Definition" metric and the second one combines the Name with value = null into the "Payload". After this I clean up the messages from both outputs and finally send them back through a 3rd join to represent the final message with both the joined "Definition" and "Payload" pieces. I can send this into SparkPlugB and it is fine. Here is that output.
Now the added function node after the DB response is my attempt at cleaning up this process. I've tried a few variations where I get the proper message but for only 1 original object, therefore it's not looping through, or I get what this one spits out where it appears to be looping but not grabbing the variables. Since this one was the closest to the structure I need I've left it on my flow for your review, although it could be way off on the proper way to do this. I greatly appreciate anyone's time that can point me in the right direction.
[
{
"id": "f00857b78058736d",
"type": "tab",
"label": "Flow 3",
"disabled": false,
"info": "",
"env": []
},
{
"id": "466fc1e164a90957",
"type": "function",
"z": "f00857b78058736d",
"name": "MQTT query ",
"func": "msg.payload = msg.topic = (\"SELECT`name`, `type` FROM `test`\")\nreturn msg;",
"outputs": 1,
"timeout": 0,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 290,
"y": 180,
"wires": [
[
"c5e9bfd242eb7e8a"
]
]
},
{
"id": "c5e9bfd242eb7e8a",
"type": "mysql",
"z": "f00857b78058736d",
"mydb": "4361e77decf5ef89",
"name": "",
"x": 430,
"y": 180,
"wires": [
[
"855e8d8412ae2161",
"accb4a3661f35eb5"
]
]
},
{
"id": "3dc9a21dad03991d",
"type": "inject",
"z": "f00857b78058736d",
"name": "",
"props": [
{
"p": "payload"
},
{
"p": "topic",
"vt": "str"
}
],
"repeat": "",
"crontab": "",
"once": false,
"onceDelay": 0.1,
"topic": "",
"payload": "",
"payloadType": "date",
"x": 140,
"y": 180,
"wires": [
[
"466fc1e164a90957"
]
]
},
{
"id": "855e8d8412ae2161",
"type": "split",
"z": "f00857b78058736d",
"name": "",
"splt": "\\n",
"spltType": "str",
"arraySplt": 1,
"arraySpltType": "len",
"stream": false,
"addname": "",
"x": 550,
"y": 180,
"wires": [
[
"3bda1db8f60c0353"
]
]
},
{
"id": "8fd01ccb5ad6b1b9",
"type": "join",
"z": "f00857b78058736d",
"name": "definition",
"mode": "custom",
"build": "merged",
"property": "definition",
"propertyType": "msg",
"key": "topic",
"joiner": "\\n",
"joinerType": "str",
"accumulate": false,
"timeout": "",
"count": "",
"reduceRight": false,
"reduceExp": "",
"reduceInit": "",
"reduceInitType": "",
"reduceFixup": "",
"x": 1000,
"y": 140,
"wires": [
[
"62db72b0797a93ef"
]
]
},
{
"id": "3bda1db8f60c0353",
"type": "function",
"z": "f00857b78058736d",
"name": "function 1",
"func": "var nameV = msg.payload.name\nvar typeV = msg.payload.type\nvar topicV = msg.topic\nvar idV = msg.parts.id\nvar countV = msg.parts.count\n\nmsg = {\n \"definition\": {[nameV]: { \"dataType\": typeV }},\n \"payload\" : {\"metrics\" : {\"name\" : nameV, \"value\" : null}},\n \"parts\": {\"id\" : idV, \"count\" : countV },\n \"topic\" : topicV\n };\nreturn msg;",
"outputs": 1,
"timeout": 0,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 680,
"y": 180,
"wires": [
[
"8fd01ccb5ad6b1b9",
"62c641a476ad3748"
]
]
},
{
"id": "0d79ab1c4956aaa8",
"type": "change",
"z": "f00857b78058736d",
"name": "",
"rules": [
{
"t": "delete",
"p": "definition",
"pt": "msg"
},
{
"t": "set",
"p": "parts.count",
"pt": "msg",
"to": "2",
"tot": "str"
},
{
"t": "set",
"p": "parts.index",
"pt": "msg",
"to": "1",
"tot": "str"
}
],
"action": "",
"property": "",
"from": "",
"to": "",
"reg": false,
"x": 1160,
"y": 220,
"wires": [
[
"c69bf583a006aac2"
]
]
},
{
"id": "c69bf583a006aac2",
"type": "join",
"z": "f00857b78058736d",
"name": "",
"mode": "custom",
"build": "array",
"property": "topic",
"propertyType": "msg",
"key": "",
"joiner": "\\n",
"joinerType": "str",
"accumulate": false,
"timeout": "",
"count": "",
"reduceRight": false,
"reduceExp": "",
"reduceInit": "",
"reduceInitType": "",
"reduceFixup": "",
"x": 1350,
"y": 180,
"wires": [
[
"2c38445718e48dcf"
]
]
},
{
"id": "62db72b0797a93ef",
"type": "change",
"z": "f00857b78058736d",
"name": "",
"rules": [
{
"t": "delete",
"p": "payload",
"pt": "msg"
},
{
"t": "set",
"p": "parts.count",
"pt": "msg",
"to": "2",
"tot": "str"
},
{
"t": "set",
"p": "parts.index",
"pt": "msg",
"to": "0",
"tot": "str"
}
],
"action": "",
"property": "",
"from": "",
"to": "",
"reg": false,
"x": 1160,
"y": 140,
"wires": [
[
"c69bf583a006aac2"
]
]
},
{
"id": "62c641a476ad3748",
"type": "delay",
"z": "f00857b78058736d",
"name": "",
"pauseType": "delay",
"timeout": "1",
"timeoutUnits": "seconds",
"rate": "1",
"nbRateUnits": "1",
"rateUnits": "second",
"randomFirst": "1",
"randomLast": "5",
"randomUnits": "seconds",
"drop": false,
"allowrate": false,
"outputs": 1,
"x": 860,
"y": 220,
"wires": [
[
"cff26b4106df52e6"
]
]
},
{
"id": "2c38445718e48dcf",
"type": "change",
"z": "f00857b78058736d",
"name": "",
"rules": [
{
"t": "delete",
"p": "topic",
"pt": "msg"
},
{
"t": "delete",
"p": "_msgid",
"pt": "msg"
}
],
"action": "",
"property": "",
"from": "",
"to": "",
"reg": false,
"x": 1500,
"y": 180,
"wires": [
[]
]
},
{
"id": "cff26b4106df52e6",
"type": "join",
"z": "f00857b78058736d",
"name": "payload",
"mode": "custom",
"build": "array",
"property": "payload.metrics",
"propertyType": "msg",
"key": "topic",
"joiner": "\\n",
"joinerType": "str",
"accumulate": false,
"timeout": "",
"count": "",
"reduceRight": false,
"reduceExp": "",
"reduceInit": "",
"reduceInitType": "",
"reduceFixup": "",
"x": 1000,
"y": 220,
"wires": [
[
"0d79ab1c4956aaa8"
]
]
},
{
"id": "accb4a3661f35eb5",
"type": "function",
"z": "f00857b78058736d",
"name": "2",
"func": "let type = \"\";\nlet name = \"\";\nlet definition = \"\";\nlet payload = \"\";\nfor (var i = 0; i < msg.payload.length; i++) {\n\n {\n \n type = msg.payload[i].type,\n name = msg.payload[i].name,\n\n definition = definition + { [name]: { \"dataType\": type }};\n payload = payload + { \"metrics\": { \"name\": name, \"value\": null }};\n \n }\n};\n\nmsg = {definition, payload};\n\nreturn msg;",
"outputs": 1,
"timeout": 0,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 670,
"y": 320,
"wires": [
[
"341051cbe3e556c5"
]
]
},
{
"id": "341051cbe3e556c5",
"type": "debug",
"z": "f00857b78058736d",
"name": "debug",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "true",
"targetType": "full",
"statusVal": "",
"statusType": "auto",
"x": 1020,
"y": 320,
"wires": []
},
{
"id": "4361e77decf5ef89",
"type": "MySQLdatabase",
"name": "",
"host": "127.0.0.1",
"port": "3306",
"db": "MQTT",
"tz": "",
"charset": "UTF8"
}
]
