# Convert Array intoto JSON

**URL:** <https://discourse.nodered.org/t/convert-array-intoto-json/11758>\
**Category:** General\
**Created:** [2 June 2019 07:24 UTC](https://discourse.nodered.org/t/convert-array-intoto-json/11758 "2019-06-02T07:24:34Z")\
**Posts on this page:** 14\
**Page:** 1

<div class="post-metadata">

**Author:** ![olab](https://avatars.discourse-cdn.com/v4/letter/o/d07c76/32.png) [@olab](https://discourse.nodered.org/u/olab)\
**Post date:** [2 June 2019 07:24 UTC](https://discourse.nodered.org/t/convert-array-intoto-json/11758/1 "2019-06-02T07:24:34Z")

</div>

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?

![Capture1](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/b/b7acba76b6ff602ed67be3e3f35575b2370dc140.png)

![Capture2](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/2/2bb27a69b1f730defd54e1f721d878c47ae42119.png)

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [2 June 2019 07:41 UTC](https://discourse.nodered.org/t/convert-array-intoto-json/11758/2 "2019-06-02T07:41:29Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![olab](https://avatars.discourse-cdn.com/v4/letter/o/d07c76/32.png) [@olab](https://discourse.nodered.org/u/olab)\
**Post date:** [2 June 2019 07:44 UTC](https://discourse.nodered.org/t/convert-array-intoto-json/11758/3 "2019-06-02T07:44:41Z")

</div>

> [@Colin](#):
>
> 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 🙂

---

<div class="post-metadata">

**Author:** ![dceejay](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dceejay/32/38_2.png) [@dceejay](https://discourse.nodered.org/u/dceejay)\
**Post date:** [2 June 2019 09:08 UTC](https://discourse.nodered.org/t/convert-array-intoto-json/11758/4 "2019-06-02T09:08:30Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![olab](https://avatars.discourse-cdn.com/v4/letter/o/d07c76/32.png) [@olab](https://discourse.nodered.org/u/olab)\
**Post date:** [2 June 2019 09:16 UTC](https://discourse.nodered.org/t/convert-array-intoto-json/11758/5 "2019-06-02T09:16:24Z")

</div>

> [@dceejay](#):
>
> 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

 ![Capture3](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/7/7c658fa32a91bcff9a8186d2bb451de4b39351fb.png)

I want to make a bar graph out of this using chartjs-vertical-bar node

 ![Capture4](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/3/335e810eba13d69bec9ae073fe2ec8faecf6195a.png)

---

<div class="post-metadata">

**Author:** ![dceejay](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dceejay/32/38_2.png) [@dceejay](https://discourse.nodered.org/u/dceejay)\
**Post date:** [2 June 2019 09:20 UTC](https://discourse.nodered.org/t/convert-array-intoto-json/11758/6 "2019-06-02T09:20:11Z")

</div>

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 ?

---

<div class="post-metadata">

**Author:** ![olab](https://avatars.discourse-cdn.com/v4/letter/o/d07c76/32.png) [@olab](https://discourse.nodered.org/u/olab)\
**Post date:** [2 June 2019 09:25 UTC](https://discourse.nodered.org/t/convert-array-intoto-json/11758/7 "2019-06-02T09:25:44Z")

</div>

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

![Capture5](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/0/096fa95d5e1552abba7e0283ea47ca8c4bec69d6.png)

---

<div class="post-metadata">

**Author:** ![Andrei](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/andrei/32/10446_2.png) [@Andrei](https://discourse.nodered.org/u/Andrei)\
**Post date:** [2 June 2019 14:59 UTC](https://discourse.nodered.org/t/convert-array-intoto-json/11758/8 "2019-06-02T14:59:14Z")

</div>

I always recommend to read and understand the chart documentation. [Click here for the link.](https://github.com/node-red/node-red-dashboard/blob/master/Charts.md)

There is a nice example at the end of the doc that shows plenty of examples.

You want something like this one:

![bar-01](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/5/5ade757cfc5cf16920d560567e141697ed77b385.png)

So, it is just a matter of copy and understand the code that generates such a chart:

```auto
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:

```auto
[{"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:

 ![bar-02](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/1/16edaec01dd7cbef9e20f39dec823155b7db978d.png)

PS: You will have troubles because of the range of the values (from 0 to 81660 in the example). It will not display nice.

---

<div class="post-metadata">

**Author:** ![olab](https://avatars.discourse-cdn.com/v4/letter/o/d07c76/32.png) [@olab](https://discourse.nodered.org/u/olab)\
**Post date:** [2 June 2019 15:47 UTC](https://discourse.nodered.org/t/convert-array-intoto-json/11758/9 "2019-06-02T15:47:13Z")

</div>

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: ![Capture2](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/2/2bb27a69b1f730defd54e1f721d878c47ae42119.png)

My problem is to turn my sql result set into this structure.

Kind regards

O

---

<div class="post-metadata">

**Author:** ![Andrei](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/andrei/32/10446_2.png) [@Andrei](https://discourse.nodered.org/u/Andrei)\
**Post date:** [2 June 2019 16:10 UTC](https://discourse.nodered.org/t/convert-array-intoto-json/11758/10 "2019-06-02T16:10:07Z")

</div>

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).

---

<div class="post-metadata">

**Author:** ![olab](https://avatars.discourse-cdn.com/v4/letter/o/d07c76/32.png) [@olab](https://discourse.nodered.org/u/olab)\
**Post date:** [2 June 2019 16:15 UTC](https://discourse.nodered.org/t/convert-array-intoto-json/11758/11 "2019-06-02T16:15:46Z")

</div>

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"}]`

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [2 June 2019 16:21 UTC](https://discourse.nodered.org/t/convert-array-intoto-json/11758/12 "2019-06-02T16:21:03Z")

</div>

As requested - look at the debug output - it matches your requirement of changing rows of objects to an object of the requested format...

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/e/ef9dd8b3a3f5e3982288acb9438a8ca6a570c8ac.png)

the flow..

```auto
[{"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":[]}]

```

---

<div class="post-metadata">

**Author:** ![olab](https://avatars.discourse-cdn.com/v4/letter/o/d07c76/32.png) [@olab](https://discourse.nodered.org/u/olab)\
**Post date:** [2 June 2019 16:31 UTC](https://discourse.nodered.org/t/convert-array-intoto-json/11758/13 "2019-06-02T16:31:54Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [2 June 2019 16:41 UTC](https://discourse.nodered.org/t/convert-array-intoto-json/11758/14 "2019-06-02T16:41:34Z")

</div>

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.