# From SQL database to UI bar chart

**URL:** <https://discourse.nodered.org/t/from-sql-database-to-ui-bar-chart/60722>\
**Category:** General\
**Tags:** node-red-dashboard, database\
**Created:** [3 April 2022 14:00 UTC](https://discourse.nodered.org/t/from-sql-database-to-ui-bar-chart/60722 "2022-04-03T14:00:41Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![jbudd](https://avatars.discourse-cdn.com/v4/letter/j/5f8ce5/32.png) [@jbudd](https://discourse.nodered.org/u/jbudd)\
**Post date:** [3 April 2022 14:00 UTC](https://discourse.nodered.org/t/from-sql-database-to-ui-bar-chart/60722/1 "2022-04-03T14:00:41Z")

</div>

I have an SQL query which produces this output:

```auto
[{"id":1,"month":"january","temperature":65},{"id":2,"month":"january","temperature":28},{"id":3,"month":"january","temperature":38},{"id":4,"month":"february","temperature":59},{"id":5,"month":"february","temperature":48},{"id":6,"month":"february","temperature":38},{"id":7,"month":"march","temperature":80},{"id":8,"month":"march","temperature":40},{"id":9,"month":"march","temperature":20},{"id":10,"month":"april","temperature":81},{"id":11,"month":"april","temperature":19},{"id":12,"month":"april","temperature":49},{"id":13,"month":"may","temperature":56},{"id":14,"month":"may","temperature":86},{"id":15,"month":"may","temperature":45},{"id":16,"month":"june","temperature":55},{"id":17,"month":"june","temperature":27},{"id":18,"month":"june","temperature":40},{"id":19,"month":"july","temperature":40},{"id":20,"month":"july","temperature":90},{"id":21,"month":"july","temperature":20}]

```

I want to display it as this chart (data and chart form one of the examples at the library flow [Populate full dashboard chart](https://flows.nodered.org/flow/2938d98507c81648a2ab5ac1ba6e7d31) )

 ![Untitled 1](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/9/8/98487a329aa7a6cfd387c308d3c6ef2f74c5db7d.jpeg)

So it has to be converted to this format

```auto
var m={};
m.labels = ["January", "February", "March", "April", "May", "June", "July"];
m.series = ['Series A', 'Series B', 'Series C'];
m.data = [
    [65, 59, 80, 81, 56, 55, 40],
    [28, 48, 40, 19, 86, 27, 90],
    [38, 28, 20, 49, 45, 60, 20]
  ];
return {payload:[m]};

```

I can't work out how to transform the SQL output into the chart input arrays, can anyone help?  
Edit - I can change the SQL to also output "Series A" etc, not sure if that helps.

---

<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:** [3 April 2022 15:53 UTC](https://discourse.nodered.org/t/from-sql-database-to-ui-bar-chart/60722/2 "2022-04-03T15:53:06Z")

</div>

Something like this jbudd.

```auto
const series_count = msg.payload.reduce((a,v) => (v.month === msg.payload[0].month ? a+1 : a),0)

let labels =[];
let series =[];
let data = [];
for(let h=0; h<series_count; h++){
    series.push("series " + (h+1));
    data[h] = [];
}

for(let i=0; i<msg.payload.length; i += series_count){
    labels.push(msg.payload[i].month);
    for(let j=0; j<series_count; j++){
        data[j].push(msg.payload[i+j].temperature);
    }
}

msg.payload=[{
    series,labels,data
    }
]
return msg;

```

---

<div class="post-metadata">

**Author:** ![jbudd](https://avatars.discourse-cdn.com/v4/letter/j/5f8ce5/32.png) [@jbudd](https://discourse.nodered.org/u/jbudd)\
**Post date:** [3 April 2022 16:29 UTC](https://discourse.nodered.org/t/from-sql-database-to-ui-bar-chart/60722/3 "2022-04-03T16:29:22Z")

</div>

Oh thats excellent, thanks @E1cid.  
I was trying to process each payload atom individually, I wish I'd thought of doing it in chunks with _i += series\_count_, so much easier!

Here is your function again prolifically annotated for my future reference.

```auto
const series_count = msg.payload.reduce((a,v) => (v.month === msg.payload[0].month ? a+1 : a),0)
// series_count = 3

let labels =[];
let series =[];
let data = [];

// Setup Series
for(let h=0; h<series_count; h++){
    series.push("series " + (h+1));
    data[h] = [];
}
// now series = ["Series 1", "Series 2", "Series 3"] 
// data = [[], [], [] ]

for(let i=0; i<msg.payload.length; i += series_count){
    // (msg.payload.length = 21)
    // msg.payload[n] = eg {"id":1,"month":"january","temperature":65}
    // msg.payload[n+1] = eg {"id":2,"month":"january","temperature":28}
    // msg.payload[n+2] = eg {"id":3,"month":"january","temperature":38}
    labels.push(msg.payload[i].month);
    for(let j=0; j<series_count; j++){
        data[j].push(msg.payload[i+j].temperature);
        //data[0].push(65)
        //data[1].push(28)
        //data[2].push(38)
    }
}
// now labels = ["january", "february", ... "july"] 
// data = [[65,59,80,81,56,55,40],[28,48,40,19,86,27,90],[38,38,20,49,45,40,20]]

msg.payload=[{
    series,labels,data
    }
]
return msg;

```

---

<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:** [17 April 2022 16:29 UTC](https://discourse.nodered.org/t/from-sql-database-to-ui-bar-chart/60722/4 "2022-04-17T16:29:36Z")

</div>

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