Hi,
I need to convert a sql result from an array into JSON like this to present in a graph
How can I solve that?
Are you sure you want it as a JSON string? I would have expected that you would need it as a javascript object.
Whichever, you first want to build it as an object then, if necesarry, use a JSON node to convert it.
You can no doubt use JSONata in a Change node to build the object, alternatively use a Function node to do that.
There are lots of things I do not understand in JSON,JS...... Yet. 
You are probably right. I simply wan't convert my array into a format for my graph
from your first debug output - how does (for example) Blanks end up with the value 10 in the output object ? there doesn't seem to be any useful values there to work off.
The first debug is the result from the SQL connection returning a gruop by query.
Fields dtReason on x-axis, DownTime as values
I want to make a bar graph out of this using chartjs-vertical-bar node
so Blanks should be 81660 not 10 ?
Where do Error1 and erro2 come from.
or we just have to guess numbers out of the air ?
M[quote="dceejay, post:6, topic:11758, full:true"]
so Blanks should be 81660 not 10 ?
Where do Error1 and erro2 come from.
or we just have to guess numbers out of the air ?
[/quote]
Sorry about that. My sample data is a mix of hardcoded data.. So, forget the actual values
It should be based on this result. Making (in this sample) 10 bars
I always recommend to read and understand the chart documentation. Click here for the link.
There is a nice example at the end of the doc that shows plenty of examples.
You want something like this one:
So, it is just a matter of copy and understand the code that generates such a chart:
var m = {};
m.labels = ["Download", "In-Store", "Mail-Order"];
m.data = [[300, 500, 100]];
m.series = ["Sales"];
return {payload:[m],topic:msg.topic};
You want to generate three arrays. It can be easily done with JSONata inside a change node.
Here is a code for learning purposes:
[{"id":"f00fcec5.24495","type":"tab","label":"Flow 3","disabled":false,"info":""},{"id":"cc8ef6b8.9f6908","type":"function","z":"f00fcec5.24495","name":"Format chart","func":"var m = {};\nm.labels =msg.payx;\nm.data = [msg.payy];\nm.series = [\"Chart\"];\nreturn {payload:[m]};\n","outputs":1,"noerr":0,"x":510,"y":120,"wires":[["c5e34ad3.ece9a8","886763c1.ea742"]]},{"id":"c5e34ad3.ece9a8","type":"ui_chart","z":"f00fcec5.24495","name":"","group":"d4cc65a.8bf5b98","order":3,"width":0,"height":0,"label":"chart","chartType":"bar","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":710,"y":120,"wires":[[]]},{"id":"1982f375.2a6f3d","type":"inject","z":"f00fcec5.24495","name":"Dataset","topic":"","payload":"{\"channel\":\"TP01\",\"color\":\"Blue\",\"dataset\":[{\"x\":\"Blanks\",\"y\":10},{\"x\":\"Feilbetjening\",\"y\":8},{\"x\":\"Funkded\",\"y\":30},{\"x\":\"Gikk Skeiss\",\"y\":8},{\"x\":\"Lim\",\"y\":50}]}","payloadType":"json","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":150,"y":120,"wires":[["9675c453.fb2308"]]},{"id":"886763c1.ea742","type":"debug","z":"f00fcec5.24495","name":"Debug1","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":720,"y":160,"wires":[]},{"id":"9675c453.fb2308","type":"change","z":"f00fcec5.24495","name":"JSONata","rules":[{"t":"set","p":"payx","pt":"msg","to":"**.x","tot":"jsonata"},{"t":"set","p":"payy","pt":"msg","to":"**.y","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":300,"y":120,"wires":[["cc8ef6b8.9f6908"]]},{"id":"d4cc65a.8bf5b98","type":"ui_group","z":"","name":"Group 1","tab":"90c6a3a3.ea2ca","disp":true,"width":"10","collapse":false},{"id":"90c6a3a3.ea2ca","type":"ui_tab","z":"","name":"Tab1","icon":"dashboard","order":6}]
It generates:
PS: You will have troubles because of the range of the values (from 0 to 81660 in the example). It will not display nice.
Hi. Andrei, and thank you for your response
Yes, I have read the documentation and I guess that when I don't understand the documentation, that's when you ask the community of this forum isn't it?...
I have found the structure of the data for the graph. Btw. it's not the ui_graph it is the chartjs-vertical-bar node. The format is supposed to look like this:
My problem is to turn my sql result set into this structure.
Kind regards
O
1 Like
I see now. I apologise for the confusion. In such case it seems straightforward. As Colin mentioned you can use JSONata in a change (or function) node to do the data transformation. It helps if you copy the payload you get from the sql result and make it available in your post , in a format that we can copy and test (instead of print screens).
At the moment my flow looks like this and I think I'm almost there. I'm quite sure that this can be done in a simple way. The query sent to the SQL is returning two columns; dtreason1 and DownTime.
[{"id":"7d8bd065.db7c7","type":"inject","z":"1dde8134.64319f","name":"","topic":"","payload":"","payloadType":"date","repeat":"5","crontab":"","once":false,"onceDelay":0.1,"x":190,"y":960,"wires":[["3c3011fa.0ab7de"]]},{"id":"3c3011fa.0ab7de","type":"MSSQL","z":"1dde8134.64319f","mssqlCN":"b7cc9dba.69d5b","name":"","query":"SELECT dtReason1, SUM(DownTime) As DownTime FROM NewMachineStateTracking WHERE dtReason1 <> '' GROUP BY dtReason1","outField":"payload","x":350,"y":960,"wires":[["4270b0a8.a4a59"]]},{"id":"4270b0a8.a4a59","type":"function","z":"1dde8134.64319f","name":"Format chart","func":"var rec = []\nvar str\nfor(let i = 0 ; i < msg.payload.length; i++){\n str = msg.payload[i].dtReason1\n str = str.trim();\n rec.push(({\"x\": str, \"y\" : msg.payload[i].DownTime }))\n}\nmsg.rec = rec\nreturn msg","outputs":1,"noerr":0,"x":510,"y":960,"wires":[["516967ce.c06f98"]]},{"id":"516967ce.c06f98","type":"json","z":"1dde8134.64319f","name":"","property":"rec","action":"str","pretty":true,"x":650,"y":960,"wires":[["5b39bcb4.cd6064"]]},{"id":"5b39bcb4.cd6064","type":"function","z":"1dde8134.64319f","name":"","func":"var pre = `{\n \"channel\": \"TP01\",\n \"color\": \"Blue\",\n \"dataset\":`\n \nvar suf = `}` \n\nmsg.payload = pre + msg.rec + suf\nreturn msg;","outputs":1,"noerr":0,"x":790,"y":960,"wires":[["6430d8cc.e18b98"]]},{"id":"6430d8cc.e18b98","type":"change","z":"1dde8134.64319f","name":"","rules":[{"t":"delete","p":"rec","pt":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":980,"y":960,"wires":[["d6cc51ab.03e68"]]},{"id":"d6cc51ab.03e68","type":"chartjs-vertical-bar","z":"1dde8134.64319f","name":"","path":"mybar","charttitle":"Fordeling av nedetid","xaxis":"Ă…rsak","yaxis":"Sekunder","x":1170,"y":1040,"wires":[[]]},{"id":"b7cc9dba.69d5b","type":"MSSQL-CN","z":"","tdsVersion":"7_4","name":"DB141004","server":"192.168.150.17","port":"1433","encyption":false,"database":"TMBrumunddal","useUTC":false,"connectTimeout":"15000","requestTimeout":"15000","cancelTimeout":"5000","pool":"10"}]
As requested - look at the debug output - it matches your requirement of changing rows of objects to an object of the requested format...
the flow..
[{"id":"e945e69a.63d228","type":"inject","z":"80855e36.822e4","name":"Simulate DB data rows","topic":"","payload":"[{\"dtReason1\":\"Blanks \",\"DownTime\":8156},{\"dtReason1\":\"tdReason Other \",\"DownTime\":123},{\"dtReason1\":\"more fake data\",\"DownTime\":321}]","payloadType":"json","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":200,"y":220,"wires":[["8fa369ed.e36aa8"]]},{"id":"8fa369ed.e36aa8","type":"function","z":"80855e36.822e4","name":"SQL Data to Ojbect","func":"var transformedData = {\n \"channel\": \"TP01\",\n \"color\": \"Blue\",\n \"dataset\": []\n}\n\n//loop through rows in SQL data\nfor(var i = 0; i < msg.payload.length; i++){\n let row = msg.payload[i];\n //make a new bojext with x+y properties\n let dataObj = {\n \"x\": row.dtReason1,\n \"y\": row.DownTime\n }\n //add the new dataObj to the datset array\n transformedData.dataset.push(dataObj)\n}\n//put result in payload\nmsg.payload = transformedData;\nreturn msg;","outputs":1,"noerr":0,"x":530,"y":220,"wires":[["5b7ae0e3.a68fd"]]},{"id":"5b7ae0e3.a68fd","type":"debug","z":"80855e36.822e4","name":"Debug1","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":720,"y":220,"wires":[]}]
2 Likes
What to say Steve-Mci??
It took you probably about 5 min...
I have been struggerling for days with this code.
I guess I still have a lot to learn 
Thank you for your help
I wish - more like 6 minutes 
Be sure to read the code in the function node (i commented it up) and try to understand why & what I did. When you come to adapt this into your flow you may need to understand whats happening.
2 Likes
