Help with messaging on sqlite DB to dashboard chart

Dashboard
1

Hello,

I'm looking for help to resolve the "Bad data inject" error when trying to pull from the sqllite node to the Chart node of the attached flow. My thought is that the array is putting out a 0: and 1: instead of x: and y:

image
image597Ă—559 10.9 KB

Here's the Function (post sqlite query and join) for those that may solve the issue without the flow.

var msg2 = [];

// this is the logic when there are multiple data sets are received
for (var i=0; i<msg.payload.length; i++) {
    var output = [];
    for (var j=0; j<msg.payload[i].length; j++) {
        output.push([msg.payload[i][j].Epoch, msg.payload[i][j].Value]);
    }
    msg2.push({ key: msg.payload[i][0].Node+"/"+msg.payload[i][0].Sensor, Value : output});
}

msg.payload=msg2;

return msg;

The entire flow is attached.
problemflow.txt (11.2 KB)

This is an adaptation of Csongor's flow
https://flows.nodered.org/flow/36f2de38ed4bb62cf3a5e8fdc76d779e

2

For line charts the format should be exactly like this:

[{
"series": ["A", "B", "C"],
"data": [
    [{ "x": 1504029632890, "y": 5 },
     { "x": 1504029636001, "y": 4 },
     { "x": 1504029638656, "y": 2 }
    ],
    [{ "x": 1504029633514, "y": 6 },
     { "x": 1504029636622, "y": 7 },
     { "x": 1504029639539, "y": 6 }
    ],
    [{ "x": 1504029634400, "y": 7 },
     { "x": 1504029637959, "y": 7 },
     { "x": 1504029640317, "y": 7 }
    ]
],
"labels": [""]
}]
3

Agreed.

Do you have a recommendation on what to update the function node to in order to set the array out to x & y instead of 0 & 1?

4

You could do it in the sql query

SELECT column_name1 AS x, column_name2 AS  y From ...
5

I couldn't get this to work. I believe the solution is in the prep function node past the sqlite node by reading this post Dynamic series identification from message, send data to Chart - #5 by Steve-Mcl

My issue is that this level of programming is past my capabilities.

6

hello ..
Im guessing that first screenshot you shared is after your attempt with the Function node ..
In order to help .. add a Debug node right after the sqlite node and share a few elements of the msg.payload array (Use the Copy Value button next to the array instead of a screenshot)

7

I hope this is what you are looking for.

EDIT - Here are a couple of the complete objects.

{"topic":"SELECT * FROM seedhouse WHERE Node='node220' AND Sensor='dev48' AND epoch >= 1651952698428 AND epoch <= 1652039098428","_msgid":"c967959b1d9bfb2b","payload":[{"sqlupdate":"2022-05-08 18:36:21","Epoch":1652034981107,"Node":"node220","Sensor":"dev48","Value":93.99},{"sqlupdate":"2022-05-08 18:52:27","Epoch":1652035947269,"Node":"node220","Sensor":"dev48","Value":96.13},{"sqlupdate":"2022-05-08 19:08:30","Epoch":1652036910513,"Node":"node220","Sensor":"dev48","Value":96.93},{"sqlupdate":"2022-05-08 19:24:33","Epoch":1652037873650,"Node":"node220","Sensor":"dev48","Value":98.29},{"sqlupdate":"2022-05-08 19:40:40","Epoch":1652038839900,"Node":"node220","Sensor":"dev48","Value":96.39}]}

{"topic":"SELECT * FROM seedhouse WHERE Node='node220' AND Sensor='dev49' AND epoch >= 1651952698428 AND epoch <= 1652039098428","complete":true,"_msgid":"c967959b1d9bfb2b","payload":[{"sqlupdate":"2022-05-08 18:52:27","Epoch":1652035947270,"Node":"node220","Sensor":"dev49","Value":25.9},{"sqlupdate":"2022-05-08 19:08:30","Epoch":1652036910514,"Node":"node220","Sensor":"dev49","Value":25.31},{"sqlupdate":"2022-05-08 19:24:33","Epoch":1652037873651,"Node":"node220","Sensor":"dev49","Value":24.27},{"sqlupdate":"2022-05-08 19:40:40","Epoch":1652038839901,"Node":"node220","Sensor":"dev49","Value":25.76}]}

EDIT - Just the payload

{"sqlupdate":"2022-05-08 18:36:21","Epoch":1652034981107,"Node":"node220","Sensor":"dev48","Value":93.99},{"sqlupdate":"2022-05-08 18:52:27","Epoch":1652035947269,"Node":"node220","Sensor":"dev48","Value":96.13},{"sqlupdate":"2022-05-08 19:08:30","Epoch":1652036910513,"Node":"node220","Sensor":"dev48","Value":96.93},{"sqlupdate":"2022-05-08 19:24:33","Epoch":1652037873650,"Node":"node220","Sensor":"dev48","Value":98.29},{"sqlupdate":"2022-05-08 19:40:40","Epoch":1652038839900,"Node":"node220","Sensor":"dev48","Value":96.39}

{"sqlupdate":"2022-05-08 18:52:27","Epoch":1652035947270,"Node":"node220","Sensor":"dev49","Value":25.9},{"sqlupdate":"2022-05-08 19:08:30","Epoch":1652036910514,"Node":"node220","Sensor":"dev49","Value":25.31},{"sqlupdate":"2022-05-08 19:24:33","Epoch":1652037873651,"Node":"node220","Sensor":"dev49","Value":24.27},{"sqlupdate":"2022-05-08 19:40:40","Epoch":1652038839901,"Node":"node220","Sensor":"dev49","Value":25.76}

8

Here's a Function node example for the chart data restructuring

[{"id":"de489d17915c10d3","type":"inject","z":"54efb553244c241f","name":"data from db","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"sqlupdate\":\"2022-05-08 18:36:21\",\"Epoch\":1652034981107,\"Node\":\"node220\",\"Sensor\":\"dev48\",\"Value\":93.99},{\"sqlupdate\":\"2022-05-08 18:52:27\",\"Epoch\":1652035947269,\"Node\":\"node220\",\"Sensor\":\"dev48\",\"Value\":96.13},{\"sqlupdate\":\"2022-05-08 19:08:30\",\"Epoch\":1652036910513,\"Node\":\"node220\",\"Sensor\":\"dev48\",\"Value\":96.93},{\"sqlupdate\":\"2022-05-08 19:24:33\",\"Epoch\":1652037873650,\"Node\":\"node220\",\"Sensor\":\"dev48\",\"Value\":98.29},{\"sqlupdate\":\"2022-05-08 19:40:40\",\"Epoch\":1652038839900,\"Node\":\"node220\",\"Sensor\":\"dev48\",\"Value\":96.39}]","payloadType":"json","x":250,"y":1140,"wires":[["a9e24bb8d75f5296","8577a45dffad9f3f"]]},{"id":"a9e24bb8d75f5296","type":"function","z":"54efb553244c241f","name":"function 1","func":"// [{\n// \"series\": [\"A\", \"B\", \"C\"],\n// \"data\": [\n//     [{ \"x\": 1504029632890, \"y\": 5 },\n//      { \"x\": 1504029636001, \"y\": 4 },\n//      { \"x\": 1504029638656, \"y\": 2 }\n//     ],\n//     [{ \"x\": 1504029633514, \"y\": 6 },\n//      { \"x\": 1504029636622, \"y\": 7 },\n//      { \"x\": 1504029639539, \"y\": 6 }\n//     ],\n//     [{ \"x\": 1504029634400, \"y\": 7 },\n//      { \"x\": 1504029637959, \"y\": 7 },\n//      { \"x\": 1504029640317, \"y\": 7 }\n//     ]\n// ],\n// \"labels\": [\"\"]\n// }]\n\n\nlet devData = msg.payload.map(el => {\n    return { \"x\": el.Epoch, \"y\": el.Value }\n})\n\n\nlet chartData = [{\n    \"series\": [msg.payload[0].Sensor],\n    \"data\": [devData],\n    \"labels\": [\"\"]\n}]\n\nmsg.payload = chartData;\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":510,"y":1140,"wires":[["5d5beb7da4e85fd2","324c6cbe316165b3"]]},{"id":"5d5beb7da4e85fd2","type":"ui_chart","z":"54efb553244c241f","name":"","group":"d8f75d1fb2200ff1","order":0,"width":"20","height":"13","label":"chart","chartType":"line","legend":"false","xformat":"auto","interpolate":"linear","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":1,"removeOlderPoints":"","removeOlderUnit":"3600","cutout":0,"useOneColor":false,"useUTC":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"outputs":1,"useDifferentColor":false,"className":"","x":710,"y":1140,"wires":[[]]},{"id":"8577a45dffad9f3f","type":"debug","z":"54efb553244c241f","name":"1","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":350,"y":1060,"wires":[]},{"id":"324c6cbe316165b3","type":"debug","z":"54efb553244c241f","name":"2","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":590,"y":1060,"wires":[]},{"id":"d8f75d1fb2200ff1","type":"ui_group","name":"Default","tab":"ecf6721e62c24085","order":1,"disp":true,"width":"20","collapse":false,"className":""},{"id":"ecf6721e62c24085","type":"ui_tab","name":"Home","icon":"dashboard","disabled":false,"hidden":false}]

References:
Javascript map() command

9

This is working for the selection of a single Node/Sensor. However, the flow joins the DB output when multiple items are selected, thus creating an array of data. I've found a similar post here Dynamic series identification from message, send data to Chart - #5 by Steve-Mcl; however, the output is different.

My apologies for creating more work. Your help is greatly appreciated.

[[{"{"sqlupdate":"2022-05-09 11:33:11","Epoch":1652095991966,"Node":"node201","Sensor":"dev50","Value":63.95},{"sqlupdate":"2022-05-09 11:49:18","Epoch":1652096958122,"Node":"node201","Sensor":"dev50","Value":63.95},{"sqlupdate":"2022-05-09 12:05:24","Epoch":1652097924381,"Node":"node201","Sensor":"dev50","Value":63.95}],[{"sqlupdate":"2022-05-08 12:28:23","Epoch":1652012902971,"Node":"node220","Sensor":"dev48","Value":60.91},{"sqlupdate":"2022-05-08 12:44:17","Epoch":1652013857123,"Node":"node220","Sensor":"dev48","Value":62.56},{"sqlupdate":"2022-05-08 13:00:14","Epoch":1652014814346,"Node":"node220","Sensor":"dev48","Value":64.36},{"sqlupdate":"2022-05-08 13:16:08","Epoch":1652015768491,"Node":"node220","Sensor":"dev48","Value":66.51},]]

image
image278Ă—872 6.97 KB

10

As I pointed out earlier this could be done in the sql query.
here is an example, replace the change node with your sqlite node.

[{"id":"e5025b32b4af5f4b","type":"ui_form","z":"bf9e1e33.030598","name":"Data points","label":"","group":"1e17cd0b.862863","order":0,"width":0,"height":0,"options":[{"label":"Period: today","value":"period/today","type":"checkbox","required":false,"rows":null},{"label":"Period: yesterday","value":"period/yesterday","type":"checkbox","required":false,"rows":null},{"label":"Period: this week","value":"period/week","type":"checkbox","required":false,"rows":null},{"label":"Node 201 Battery","value":"node201/dev04","type":"checkbox","required":false,"rows":null},{"label":"Node 201 Temperature","value":"node201/dev50","type":"checkbox","required":false,"rows":null},{"label":"Node 201 RSSI","value":"node201/dev02","type":"checkbox","required":false,"rows":null},{"label":"Node 220 Battery","value":"node220/dev04","type":"checkbox","required":false,"rows":null},{"label":"Node 220 Temperature","value":"node220/dev48","type":"checkbox","required":false,"rows":null},{"label":"Node 220 Humidity","value":"node220/dev49","type":"checkbox","required":false,"rows":null},{"label":"Node 220 RSSI","value":"node220/dev02","type":"checkbox","required":false,"rows":null}],"formValue":{"period/today":false,"period/yesterday":false,"period/week":false,"node201/dev04":false,"node201/dev50":false,"node201/dev02":false,"node220/dev04":false,"node220/dev48":false,"node220/dev49":false,"node220/dev02":false},"payload":"","submit":"Submit","cancel":"Cancel","topic":"","topicType":"str","splitLayout":false,"className":"","x":135,"y":1367,"wires":[["edd704eac37c28c1"]]},{"id":"edd704eac37c28c1","type":"function","z":"bf9e1e33.030598","name":"SQL","func":"// This will handle any device and any attribute as long as it is in the DB\nvar Week  = 604800000 ; //7 Days\nvar Day   =  86400000 ; // 1 Days\nvar d = new Date();\nvar epoch = d.getTime();\nvar fromdate = epoch - Day;\nvar enddate = epoch;\nvar output = [];\n\nfor (const property in msg.payload) {\n    if (msg.payload.hasOwnProperty(property)) {\n        //output.push({ payload: property + \" has value \"+msg.payload[property] });\n        if (msg.payload[property]) {\n            var parts = property.split(\"/\");\n            if (parts[0]===\"period\") {\n                switch (parts[1]) {\n                    case \"today\":\n                        fromdate = epoch-Day;\n                        enddate = epoch;\n                        break;\n                    case \"yesterday\":\n                        fromdate = epoch-2*Day;\n                        enddate = epoch-Day;\n                        break;\n                    case \"week\":\n                        fromdate = epoch-Week;\n                        enddate = epoch;\n                        break;\n                }\n            } else {\n                output.push({ \n                    device : property,\n                    topic: \"SELECT epoch AS x, value AS y FROM seedhouse WHERE Node='\"+parts[0]+\"' AND Sensor='\"+parts[1]+\"' AND epoch >= \" + fromdate + \" AND epoch <= \" + enddate + \" ORDER BY epoch\" });\n            }\n        }\n    }\n}\n\noutput[output.length-1].complete=true;\n\nreturn [ output ];\n\n//msg.topic = \"SELECT * FROM sensor_data WHERE device='growatt' AND sensor='power' AND epoch >= \" + fromdate + \" AND epoch <= \" + epoch ;\n//return msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":330,"y":1420,"wires":[["c161f790bb333a62","ad0ab92f.ae57c8"]]},{"id":"c161f790bb333a62","type":"debug","z":"bf9e1e33.030598","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":510,"y":1360,"wires":[]},{"id":"ad0ab92f.ae57c8","type":"change","z":"bf9e1e33.030598","name":"for testing replace with sqlite node","rules":[{"t":"set","p":"payload","pt":"msg","to":"[{\"x\":1652034981107,\"y\":$random()*10+93.99},{\"x\":1652035947269,\"y\":$random()*10+96.13}]","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":530,"y":1480,"wires":[["1168162739bfaf7d"]]},{"id":"1168162739bfaf7d","type":"join","z":"bf9e1e33.030598","name":"","mode":"custom","build":"object","property":"payload","propertyType":"msg","key":"device","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":618,"y":1421,"wires":[["63e36a68b9ad13f8","8dbf9c5f2b6c9767"]]},{"id":"63e36a68b9ad13f8","type":"function","z":"bf9e1e33.030598","name":"Prep","func":"const labels = [];\nconst data = [];\nconst series = [];\n\nfor(const key in msg.payload){\n    labels.push(key);\n    data.push(msg.payload[key]);\n    series.push(key);\n}\n\nmsg.payload =[\n    {\n        labels,\n        data,\n        series\n    }]\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":807,"y":1423,"wires":[["77ecb32dc5a5b5c1","01cbcea724a124aa"]]},{"id":"8dbf9c5f2b6c9767","type":"debug","z":"bf9e1e33.030598","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":703.75,"y":1345,"wires":[]},{"id":"77ecb32dc5a5b5c1","type":"debug","z":"bf9e1e33.030598","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":963.75,"y":1385,"wires":[]},{"id":"01cbcea724a124aa","type":"ui_chart","z":"bf9e1e33.030598","name":"Chart","group":"bd903d6.d9fc7c","order":0,"width":0,"height":0,"label":"","chartType":"line","legend":"false","xformat":" H:m a","interpolate":"linear","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":"1","removeOlderPoints":"","removeOlderUnit":"604800","cutout":"","useOneColor":false,"useUTC":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"outputs":1,"useDifferentColor":false,"className":"","x":830,"y":1540,"wires":[[]]},{"id":"457076cc.a92f18","type":"inject","z":"bf9e1e33.030598","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{ \"series\": [\"A\", \"B\", \"C\"], \"data\": [     [{ \"x\": 1504029632890, \"y\": 5 },      { \"x\": 1504029636001, \"y\": 4 },      { \"x\": 1504029638656, \"y\": 2 }     ],     [{ \"x\": 1504029633514, \"y\": 6 },      { \"x\": 1504029636622, \"y\": 7 },      { \"x\": 1504029639539, \"y\": 6 }     ],     [{ \"x\": 1504029634400, \"y\": 7 },      { \"x\": 1504029637959, \"y\": 7 },      { \"x\": 1504029640317, \"y\": 7 }     ] ], \"labels\": [\"\"] }]","payloadType":"json","x":780,"y":1280,"wires":[["77ecb32dc5a5b5c1"]]},{"id":"b9636e89d32ffa37","type":"inject","z":"bf9e1e33.030598","name":"Reset chart","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":"","topic":"","payload":"[]","payloadType":"json","x":165,"y":1543,"wires":[["01cbcea724a124aa"]]},{"id":"1e17cd0b.862863","type":"ui_group","name":"Selection","tab":"1e3fe400.0baf5c","order":1,"disp":true,"width":"5"},{"id":"bd903d6.d9fc7c","type":"ui_group","name":"Report","tab":"1e3fe400.0baf5c","order":2,"disp":true,"width":"18"},{"id":"1e3fe400.0baf5c","type":"ui_tab","name":"Reports","icon":"dashboard","order":9}]

Then there would be no need to format the data to x,y as it would be returned that way already.
ps added a order by to query just incase there is data in database that is out of sync.

2 Likes
11

Thank you so much. I've seen your contributions all over these forums. Your time and attention is much appreciated.

I'll continue to mull over the solutions in this post to get better at coding and asking the right questions.
Thank you all.

12

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.