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

General
1

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 :

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

I get the following table :
image

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 ?

2

Something like this may help you.

[{"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

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

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 '$$'

4

Use JSONata (J:) not JSON

Screenshot_20220413-195340
Screenshot_20220413-1953401920×1080 231 KB

Javascript

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

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

6

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