# Convert msg.payload from sql node to an array for bar chart

**URL:** <https://discourse.nodered.org/t/convert-msg-payload-from-sql-node-to-an-array-for-bar-chart/61250>\
**Category:** General\
**Created:** [13 April 2022 17:59 UTC](https://discourse.nodered.org/t/convert-msg-payload-from-sql-node-to-an-array-for-bar-chart/61250 "2022-04-13T17:59:14Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![cpedroni](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/cpedroni/32/59502_2.png) [@cpedroni](https://discourse.nodered.org/u/cpedroni)\
**Post date:** [13 April 2022 17:59 UTC](https://discourse.nodered.org/t/convert-msg-payload-from-sql-node-to-an-array-for-bar-chart/61250/1 "2022-04-13T17:59:14Z")

</div>

Hello, I am new to the node-red community. I am currently trying to display data from an sql database in a bar chart.  
I have an sql table with two columns : client (name of the client) and nbconnexion (number of times this client logged in).  
By doing the follwing sql query :

```auto
SELECT name as client, count(*) AS nbconnexion
FROM `ConnexionTime`
GROUP BY client
LIMIT 10

```

I get the following table :  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/c/e/ceb83752420e21c0b21b0b0451e6f8dea6b0cf2e.png)

Now I want to display this data by doing a barchart using the ui\_chart node.

How do I convert the msg.payload to an array for it to be displayed in the chart ?

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [13 April 2022 18:28 UTC](https://discourse.nodered.org/t/convert-msg-payload-from-sql-node-to-an-array-for-bar-chart/61250/2 "2022-04-13T18:28:42Z")

</div>

Something like this may help you.

```auto
[{"id":"724d496b.62dd88","type":"inject","z":"bf9e1e33.030598","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"client\":\"john\",\"nbconnexion\":11},{\"client\":\"jane\",\"nbconnexion\":12}]","payloadType":"json","x":100,"y":80,"wires":[["2c2af924.54381e"]]},{"id":"2c2af924.54381e","type":"change","z":"bf9e1e33.030598","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"[\t{\"labels\": [$$.payload.client],\t\"data\":[[$$.payload.nbconnexion]],\t \"series\":[] }]","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":330,"y":80,"wires":[["813eef8f.44d6a8","7058f197.2bcf"]]},{"id":"813eef8f.44d6a8","type":"ui_chart","z":"bf9e1e33.030598","name":"","group":"2d4fe667.28f8ba","order":16,"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,"useUTC":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"outputs":1,"useDifferentColor":false,"className":"","x":540,"y":80,"wires":[[]]},{"id":"7058f197.2bcf","type":"debug","z":"bf9e1e33.030598","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":430,"y":180,"wires":[]},{"id":"2d4fe667.28f8ba","type":"ui_group","name":"demo","tab":"1caa8458.b17814","order":1,"disp":true,"width":"12","collapse":false},{"id":"1caa8458.b17814","type":"ui_tab","name":"Demo","icon":"dashboard","order":1,"disabled":false,"hidden":false}]

```

It uses JSONata in a change node, you could do similar with Javascript in a function node.

JSONata expression

```auto
[
  {
  "labels": [$$.payload.client],
  "data":[[$$.payload.nbconnexion]],
  "series":[]  
  }
]

```

---

<div class="post-metadata">

**Author:** ![cpedroni](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/cpedroni/32/59502_2.png) [@cpedroni](https://discourse.nodered.org/u/cpedroni)\
**Post date:** [13 April 2022 18:40 UTC](https://discourse.nodered.org/t/convert-msg-payload-from-sql-node-to-an-array-for-bar-chart/61250/3 "2022-04-13T18:40:48Z")

</div>

How could I do this in a function node in Javascript. I am very familiar with JSON and when I copy/paste your expression I get a syntax error for the '$$'

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [13 April 2022 18:52 UTC](https://discourse.nodered.org/t/convert-msg-payload-from-sql-node-to-an-array-for-bar-chart/61250/4 "2022-04-13T18:52:48Z")

</div>

Use JSONata (J:) not JSON

 ![Screenshot_20220413-195340](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/5/4/54322016eece996f9db24c2a115be25edefa95a4.png)  
Javascript

```auto
msg.payload = [
  {
  "labels": msg.payload.map(e => e.client),
  "data":[msg.payload.map(e => e.nbconnexion)],
  "series":[]  
  }
]
return msg;

```

---

<div class="post-metadata">

**Author:** ![cpedroni](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/cpedroni/32/59502_2.png) [@cpedroni](https://discourse.nodered.org/u/cpedroni)\
**Post date:** [13 April 2022 19:00 UTC](https://discourse.nodered.org/t/convert-msg-payload-from-sql-node-to-an-array-for-bar-chart/61250/5 "2022-04-13T19:00:29Z")

</div>

Oh I see it works now ! Thank you for your help

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [12 June 2022 19:01 UTC](https://discourse.nodered.org/t/convert-msg-payload-from-sql-node-to-an-array-for-bar-chart/61250/6 "2022-06-12T19:01:11Z")

</div>

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