Dynamic series identification from message, send data to Chart

[Tomadoggy]

I initially posted as an add on to another thread...moving to new thread as this is a unique question.

---

Moving to the next step of selecting data from the MYSQL DB and pushing X records out to a chart. I have learned a lot and copied a lot of examples with marginal success, but have exhausted my abilities...so once again asking for assistance.

My goal is to take the outputs of the select statement which contain multiple fields/data points which use this within a template node:

SELECT Timestamp,Temp,Pressure,Humidity,Light FROM exterior_climate ORDER BY TIMESTAMP DESC LIMIT 20

Which is wired to the MYSQL node. The output is:

With of course 20 objects from the select statement.

From there I have tried dozens of different functions, formats, and set nodes to prepare this message for output to a chart but with no real success. My goal is (perhaps lofty) to dynamically set the data series from the SQL table names as moving ahead I may add fields to the database and would like Node Red to accommodate these new fields without re-coding. To-date I have only been able to get the following results:

Using the following code within a function node:

---

let r = msg.payload;
let series = [r[0]];
let data = [r.map( v => ({
"x": v.Timestamp,
"y": v.Temp
}))];

---

msg.payload = [{"series": series, "data": data}];
return msg;

Obviously only currently getting the first series of Temp data, and unable to parse the field name out of the first object.

So far, this solution has been proposed, which helps dramatically, but still hoping for a dynamic identification of the series within the message. Proposed solution

Thank you in advance for any guidance!

[Tomadoggy]

Currently using the following in a function node which is getting the data into the chart correctly...however wondering if there is a way to dynamically populate the series from the message:

---

var series1 = ;
var series2 = ;
var series3 = ;
var series4 = ;

//loop each row and build an array in the required format
for (let index = 0; index < msg.payload.length; index++) {
const e = msg.payload[index];
//var t = new Date(e.timeNRchar).valueOf()
//var t = Timestamp
var t = new Date(e.Timestamp).valueOf()
series1.push({ "x": t, "y": e.Temp });
series2.push({ "x": t, "y": e.Pressure });
series3.push({ "x": t, "y": e.Humidity });
series4.push({ "x": t, "y": e.Light });
}

msg.payload = [
{
"series": ["Temp", "Pressure","Humidity","Light"],
"data": [series1,series2,series3,series4],
//"labels": [""],
}
];

return msg;

---

[Colin]

You can iterate through the keys using

for (const key in msg.payload[0]) {
  if (msg.payload[0].hasOwnProperty(key)) {
    // the key name is in key and the value can be accessed by msg.payload[0][key]
  } 
}

The reason for the hasOwnProperty check is that if that is not used then you will get all the properties of any underlying object types that the object is based on. In this case it may not be necessary, but it is good practice to use it in order to prevent unexpected results.

[Tomadoggy]

Colin, thank you for the feedback, but even after attempting several different iterations of code/syntax, I am unable to get this working. Without seeming a mooch, would you be willing to provide additional guidance on how to construct subsequent parts of the code? It seems logical to use what you provided to ID the numerous series provided to the function, I cannot figure out how to proceed. Many Thanks!

[Steve-Mcl]

So, i am not 100% certain what you are after but if (by the title "dynamic series") you mean sending alternative types of data from a database (i.e. varying fields and varying quantities) then this should get you close.

It was a simple adaption of the previous solution I pointed you to, modified to build the series from the field names of the 1st data element in the data passed in.

There are 2 injects to simulate two different sets of mySQL data

[{"id":"227e8b13.87c804","type":"inject","z":"11942aca.455835","name":"Fake DB data","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"Timestamp\":\"2020-11-18 18:25:48.906\",\"Temp\":21.3,\"Light\":15,\"Humidity\":31.2},{\"Timestamp\":\"2020-11-18 18:16:23.957\",\"Temp\":21.6,\"Light\":16,\"Humidity\":32.2},{\"Timestamp\":\"2020-11-18 18:14:06.429\",\"Temp\":21.7,\"Light\":17,\"Humidity\":37.2},{\"Timestamp\":\"2020-11-18 18:13:50.943\",\"Temp\":21.6,\"Light\":16,\"Humidity\":44.2}]","payloadType":"json","x":410,"y":480,"wires":[["485c8d35.d133b4"]]},{"id":"485c8d35.d133b4","type":"function","z":"11942aca.455835","name":"DB Data to Chart data","func":"/* DB Data\n[\n    {\n        \"Timestamp\": \"2020-11-18 18:25:48.906\",\n        \"Temp\": 21.3,\n        \"Light\": 15,\n        \"Pressure\": 18100.2,\n        \"Humidity\": 31.2\n    },\n]\n*/\n/* Desired format\n[{\n\"series\": [\"temp\", \"humidity\"],\n\"data\": [\n    [{ \"x\": 1504029632890, \"y\": 5 }, //series 1 data\n     { \"x\": 1504029636001, \"y\": 4 },\n    ],\n    [{ \"x\": 1504029633514, \"y\": 6 }, //series 2 data\n     { \"x\": 1504029636622, \"y\": 7 },\n    ],\n],\n\"labels\": [\"\"]\n}]\n*/\n\nvar series = [];\nvar data = msg.payload;\nif (!data || !Array.isArray(data) || !data.length) {\n    node.warn(\"expected an array of data with at least 1 element\");\n    return null;\n    //alternatively return the msg with a null payload to clear graph\n}\n\nconst tsField = \"Timestamp\";\nvar _f = Object.keys(data[0]);\nvar fields = [];\nvar fi = 0;\n_f.forEach(function(elem) {\n    if (elem != tsField) {\n        fields.push(elem);\n        series[fi++] = [];\n    }\n});\n\n//loop each row and build an array in the required format\nfor (let index = 0; index < msg.payload.length; index++) {\n    const row = msg.payload[index];\n    var t = new Date(row[tsField]).valueOf()\n    var i = 0;\n    for (let f = 0; f < fields.length; f++ ) {\n        let field = fields[f];\n        series[f].push({ \"x\": t, \"y\": row[field] })\n    }\n}\n\n\nmsg.payload = [\n    {\n        \"series\": fields,\n        \"data\": series,\n        \"labels\": [\"\"]\n    }\n];\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":640,"y":480,"wires":[["9c0c0c8.bc26ff","e109c141.4a4f3"]]},{"id":"9c0c0c8.bc26ff","type":"debug","z":"11942aca.455835","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":900,"y":480,"wires":[]},{"id":"e109c141.4a4f3","type":"ui_chart","z":"11942aca.455835","name":"","group":"dce9e7a2.d20c78","order":4,"width":0,"height":0,"label":"mySQL data","chartType":"line","legend":"true","xformat":"HH:mm:ss","interpolate":"bezier","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":"2","removeOlderPoints":"1000","removeOlderUnit":"3600","cutout":0,"useOneColor":false,"useUTC":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"useOldStyle":false,"outputs":1,"x":910,"y":540,"wires":[[]]},{"id":"b6152635.e57148","type":"inject","z":"11942aca.455835","name":"Fake DB data","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"Timestamp\":\"2020-11-18 18:25:48.906\",\"Temp\":26.3,\"Light\":20,\"Humidity\":35.2,\"Pressure\":18001.2},{\"Timestamp\":\"2020-11-18 18:16:23.957\",\"Temp\":25.6,\"Light\":19,\"Humidity\":32.2,\"Pressure\":18032.2},{\"Timestamp\":\"2020-11-18 18:14:06.429\",\"Temp\":27.7,\"Light\":18,\"Humidity\":32.2,\"Pressure\":18137.2},{\"Timestamp\":\"2020-11-18 18:13:50.943\",\"Temp\":23.6,\"Light\":19,\"Humidity\":40.2,\"Pressure\":17944.2}]","payloadType":"json","x":410,"y":520,"wires":[["485c8d35.d133b4"]]},{"id":"dce9e7a2.d20c78","type":"ui_group","name":"Object detection","tab":"5132060d.4cde48","order":1,"disp":true,"width":"7","collapse":false},{"id":"5132060d.4cde48","type":"ui_tab","name":"Home","icon":"dashboard","disabled":false,"hidden":false}]

[Tomadoggy]

Steve, IMHO you are a GENIUS! For those of us who struggle to comprehend the logic and intricacies of writing code, contributors like you are invaluable! I continue to learn and benefit from individuals like you. If you had a "buy me a beer" in your profile I would certainly contribute. Not that I don't value my days of attempts and the associated learning....just so much appreciate a perfectly working solution. Many Thanks!!