Single line chart displaying MySql-data

General
1

I have this output from a MySQL query:

[{"time":"2019-07-23T18:13:24.000Z","voltage":3.26},{"time":"2019-07-23T18:13:04.000Z","voltage":3.26},{"time":"2019-07-23T18:12:44.000Z","voltage":3.26},{"time":"2019-07-23T18:12:04.000Z","voltage":3.26},{"time":"2019-07-23T18:11:44.000Z","voltage":3.26},{"time":"2019-07-23T18:11:23.000Z","voltage":3.26},{"time":"2019-07-23T18:11:03.000Z","voltage":3.26},{"time":"2019-07-23T18:10:43.000Z","voltage":3.26},{"time":"2019-07-23T18:10:22.000Z","voltage":3.26},{"time":"2019-07-23T18:10:02.000Z","voltage":3.26}]

I try to display this with the dashboard ui_chart node, but get "bad data inject" from debug window.
What am I missing here?

Example flow (array put into inject node):

[{"id":"cce7a5b3.8ffaf8","type":"tab","label":"chart test","disabled":false,"info":""},{"id":"11e50b1e.086525","type":"inject","z":"cce7a5b3.8ffaf8","name":"inject array","topic":"","payload":"[{\"time\":\"2019-07-23T18:13:24.000Z\",\"voltage\":3.26},{\"time\":\"2019-07-23T18:13:04.000Z\",\"voltage\":3.26},{\"time\":\"2019-07-23T18:12:44.000Z\",\"voltage\":3.26},{\"time\":\"2019-07-23T18:12:04.000Z\",\"voltage\":3.26},{\"time\":\"2019-07-23T18:11:44.000Z\",\"voltage\":3.26},{\"time\":\"2019-07-23T18:11:23.000Z\",\"voltage\":3.26},{\"time\":\"2019-07-23T18:11:03.000Z\",\"voltage\":3.26},{\"time\":\"2019-07-23T18:10:43.000Z\",\"voltage\":3.26},{\"time\":\"2019-07-23T18:10:22.000Z\",\"voltage\":3.26},{\"time\":\"2019-07-23T18:10:02.000Z\",\"voltage\":3.26}]","payloadType":"json","repeat":"","crontab":"","once":true,"onceDelay":0.1,"x":214,"y":155,"wires":[["d6be3d5e.2fe3d","fbefbe0.4cf9d4"]]},{"id":"d6be3d5e.2fe3d","type":"ui_chart","z":"cce7a5b3.8ffaf8","name":"line chart","group":"f02a6ce.5981f9","order":1,"width":0,"height":0,"label":"chart","chartType":"line","legend":"false","xformat":"HH:mm:ss","interpolate":"linear","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":1,"removeOlderPoints":"","removeOlderUnit":"3600","cutout":0,"useOneColor":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"useOldStyle":false,"outputs":1,"x":426,"y":158,"wires":[["f88aaee7.8455"]]},{"id":"fbefbe0.4cf9d4","type":"debug","z":"cce7a5b3.8ffaf8","name":"complete msg","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":447,"y":121,"wires":[]},{"id":"f88aaee7.8455","type":"debug","z":"cce7a5b3.8ffaf8","name":"complete msg","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":674,"y":119,"wires":[]},{"id":"f02a6ce.5981f9","type":"ui_group","z":"","name":"TEST","tab":"96eb0793.053038","order":2,"disp":true,"width":"12","collapse":false},{"id":"96eb0793.053038","type":"ui_tab","z":"b317d85f.b86b38","name":"Home2","icon":"dashboard"}]
2

I have manually edited the data to fit the requirements for chart_ui.

{"_msgid":"d06e93.606b617","topic":"","payload":[{"series":["y"],"data":[[{"x":1563998854561,"y":3.26},{"x":1563998855571,"y":3.26},{"x":1563998856581,"y":3.26},{"x":1563998857591,"y":3.26},{"x":1563998858601,"y":3.26},{"x":1563998859611,"y":3.26},{"x":1563998856021,"y":3.26},{"x":1563998856131,"y":3.26},{"x":1563998856241,"y":3.26},{"x":1563998856351,"y":3.26}]],"labels":["y"]}]}

So now it shows up as a chart as expected. Now I just have to figure out how to get there via a function (or other) node...

Any help is appreciated!

3

Your second flow in not importing
what does the data coming from the database look like?

4

Thanks for your response!
The code in the second post is not a flow, but a modified copy of the payload coming from the MySQL node, meant to be placed into an injection node, feeding a chart node and debug node(s) for testing.

Waiting for response I tried a few more times to get the payload formatted as needed, and now I have this function:

var i;
var x = [];
var y = [];
var pl_old = msg.payload;
var pl_new = [];

for (i = 0; i<pl_old.length; i++) {
    // get data info x and y, and convert to unix time format:
    x[i] = new Date(pl_old[i].time).getTime();
    y[i] = pl_old[i].voltage;
    
    // generate key-value pair:
    pl_new[i] = {"x":x[i], "y":y[i]};
}

// Redefine msg.payload and return:
msg.payload = [{"series":["voltage"], "labels":["voltage data"], "data":[pl_new]}];

return msg;

which seems to work very well !

It may contain errors, be more clever written, etc, but i am a newbie so please give feedback if there are better ways to do this so I can learn from it.

Regards,

5

But it is not importable. It looks like you missed something in the copy/paste of the node.

As for the function, there are many ways to solve problems. If this is working then good for you :star2:

6

Hi, again!

The code supplied was not meant for import as such, but if I start with a new flow, add the inject node and paste the given code into the payload file as json, it can then be injected into other nodes. In my case chart and debug nodes for testing. But of course I could (and perhaps should) have given you the complete node or maybe flow for import, not only the json, to make it easier and more safe to submit the case. But hopefully a lesson learned, and thank you for your time with my challenge!.