# Show postgresql data on chart in nodered dashboard
**URL:** https://discourse.nodered.org/t/show-postgresql-data-on-chart-in-nodered-dashboard/72706
**Category:** Dashboard
**Created:** [26 December 2022 06:19 UTC](https://discourse.nodered.org/t/show-postgresql-data-on-chart-in-nodered-dashboard/72706 "2022-12-26T06:19:45Z")
**Posts on this page:** 20
**Page:** 1
### Author:  [@sharad](https://discourse.nodered.org/u/sharad)
#### Post date: [26 December 2022 06:19 UTC](https://discourse.nodered.org/t/show-postgresql-data-on-chart-in-nodered-dashboard/72706/1 "2022-12-26T06:19:45Z")
Hello All,
I wanted to show the data come from postgresql on nodered chart. My data is came from postgresql in below screenshot format so how I show this into chart? I want to show value on y-axis and date on x-axis. please help me.
---
### Author:  [@bakman2](https://discourse.nodered.org/u/bakman2)
#### Post date: [26 December 2022 06:41 UTC](https://discourse.nodered.org/t/show-postgresql-data-on-chart-in-nodered-dashboard/72706/2 "2022-12-26T06:41:02Z")
The output is coming directly from postgres, no data changes were made ?
I would expect an array of data.
---
### Author:  [@sharad](https://discourse.nodered.org/u/sharad)
#### Post date: [26 December 2022 06:51 UTC](https://discourse.nodered.org/t/show-postgresql-data-on-chart-in-nodered-dashboard/72706/3 "2022-12-26T06:51:52Z")
Hello @bakman2 . below is my flow. I convert postgres data into json using function node. please refer below flow. but if I give this function output to chart then nothing shown.
```auto
[
{
"id": "a8456abe9dc2476c",
"type": "inject",
"z": "48acf0a74b57fcc9",
"name": "",
"props": [
{
"p": "payload"
},
{
"p": "topic",
"vt": "str"
}
],
"repeat": "",
"crontab": "",
"once": false,
"onceDelay": 0.1,
"topic": "",
"payload": "SELECT LAST_VALUE (\"aqs1_eco2\") over(order by \"aqs1_datetime\" desc) y,\"aqs1_datetime\" x FROM \"air_quality\" where \"aqs1_eco2\" !='NaN' limit 10",
"payloadType": "str",
"x": 150,
"y": 740,
"wires": [
[
"92b824a670efeb1d"
]
]
},
{
"id": "be6125e12a05d6b3",
"type": "debug",
"z": "48acf0a74b57fcc9",
"name": "debug 14",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "false",
"statusVal": "",
"statusType": "auto",
"x": 760,
"y": 740,
"wires": []
},
{
"id": "92b824a670efeb1d",
"type": "postgres",
"z": "48acf0a74b57fcc9",
"postgresdb": "d91d328a1cfd9505",
"name": "",
"output": true,
"perrow": false,
"rowspermsg": "1",
"return_on_error": false,
"limit_queries": "0",
"limit_by": "payload",
"limit_value": "1",
"limit_drop_intermediate": false,
"limit_drop_if_in_queue": false,
"outputs": true,
"x": 380,
"y": 720,
"wires": [
[
"fcd97642c5882615"
]
]
},
{
"id": "fcd97642c5882615",
"type": "function",
"z": "48acf0a74b57fcc9",
"name": "function 47",
"func": "var arr = msg.payload; // get the array\nvar rv = {};// create a new empty object\nfor (var i = 0; i < arr.length; ++i) {\n let key = (i + 1); //built a key \n rv[key] = arr[i]; // set value in New object\n}\nmsg.payload = [{\n \"series\": [\"A\"],\n \"data\": [rv],\n \"labels\": [\"\"]\n}]\n\nreturn msg;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 570,
"y": 720,
"wires": [
[
"be6125e12a05d6b3",
"d8c79b20b887df16"
]
]
},
{
"id": "d91d328a1cfd9505",
"type": "postgresdb",
"cfgname": "",
"hostname": "localhost",
"port": "5432",
"db": "postgres",
"ssl": false
}
]
```
---
### Author:  [@bakman2](https://discourse.nodered.org/u/bakman2)
#### Post date: [26 December 2022 06:55 UTC](https://discourse.nodered.org/t/show-postgresql-data-on-chart-in-nodered-dashboard/72706/4 "2022-12-26T06:55:54Z")
In the function node you are creating a new object (why ?) while the chart expects an array.
---
### Author:  [@sharad](https://discourse.nodered.org/u/sharad)
#### Post date: [26 December 2022 07:04 UTC](https://discourse.nodered.org/t/show-postgresql-data-on-chart-in-nodered-dashboard/72706/5 "2022-12-26T07:04:44Z")
yess but when i directly connect postgresql output to chart then also nothing happens. can you tell me in which format we have to modify the array which is got from postgresql node.
---
### Author:  [@bakman2](https://discourse.nodered.org/u/bakman2)
#### Post date: [26 December 2022 07:25 UTC](https://discourse.nodered.org/t/show-postgresql-data-on-chart-in-nodered-dashboard/72706/6 "2022-12-26T07:25:22Z")
See the [chart documentation](https://github.com/node-red/node-red-dashboard/blob/master/Charts.md).
---
### Author:  [@E1cid](https://discourse.nodered.org/u/E1cid)
#### Post date: [26 December 2022 08:35 UTC](https://discourse.nodered.org/t/show-postgresql-data-on-chart-in-nodered-dashboard/72706/7 "2022-12-26T08:35:26Z")
Your query looks a little off
```auto
SELECT LAST_VALUE ("aqs1_eco2") over(order by "aqs1_datetime" desc) y,"aqs1_datetime" x FROM "air_quality" where "aqs1_eco2" !='NaN' limit 10
```
You may want to add AS to it. e.g.
```auto
SELECT LAST_VALUE ("aqs1_eco2") over(order by "aqs1_datetime" desc) AS y,"aqs1_datetime" AS x FROM "air_quality" where "aqs1_eco2" !='NaN' limit 10
```
Then your returned array should have the correct property names, and you should be able to use it in your function to create the chart array
e.g.
```auto
msg.payload = [{
"series": ["A"],
"data": [msg.payload],
"labels": [""]
}]
return msg;
```
---
### Author:  [@sharad](https://discourse.nodered.org/u/sharad)
#### Post date: [26 December 2022 08:55 UTC](https://discourse.nodered.org/t/show-postgresql-data-on-chart-in-nodered-dashboard/72706/8 "2022-12-26T08:55:46Z")
Hey @E1cid but without using AS query work properly. there is no issue in query but I want array format for chart
---
### Author:  [@E1cid](https://discourse.nodered.org/u/E1cid)
#### Post date: [26 December 2022 09:00 UTC](https://discourse.nodered.org/t/show-postgresql-data-on-chart-in-nodered-dashboard/72706/9 "2022-12-26T09:00:17Z")
Yes and as said if you add AS the returned array payload should look like.
```auto
[{"x":"2022-12-12T12:12:12.0000Z","y":6},{"x":"2022-12-13T12:12:12.0000Z","y":8}]
```
With correct property names "x" and "y"
---
### Author:  [@sharad](https://discourse.nodered.org/u/sharad)
#### Post date: [26 December 2022 09:08 UTC](https://discourse.nodered.org/t/show-postgresql-data-on-chart-in-nodered-dashboard/72706/10 "2022-12-26T09:08:40Z")
yes. but when I pass this to chart node it not shown any value.
---
### Author:  [@E1cid](https://discourse.nodered.org/u/E1cid)
#### Post date: [26 December 2022 09:09 UTC](https://discourse.nodered.org/t/show-postgresql-data-on-chart-in-nodered-dashboard/72706/11 "2022-12-26T09:09:47Z")
Yes, but did you format it as the function example i gave?
---
### Author:  [@sharad](https://discourse.nodered.org/u/sharad)
#### Post date: [26 December 2022 09:18 UTC](https://discourse.nodered.org/t/show-postgresql-data-on-chart-in-nodered-dashboard/72706/12 "2022-12-26T09:18:30Z")
yes I tried this but no output shown.
---
### Author:  [@E1cid](https://discourse.nodered.org/u/E1cid)
#### Post date: [26 December 2022 09:19 UTC](https://discourse.nodered.org/t/show-postgresql-data-on-chart-in-nodered-dashboard/72706/13 "2022-12-26T09:19:43Z")
Show us, supply a copyable output of the db node and the function node. And the contents of your function node.
---
### Author:  [@sharad](https://discourse.nodered.org/u/sharad)
#### Post date: [26 December 2022 09:29 UTC](https://discourse.nodered.org/t/show-postgresql-data-on-chart-in-nodered-dashboard/72706/14 "2022-12-26T09:29:02Z")
Yes sure.
1. DB node output
```auto
[{"x":"2022-12-23T11:11:10.855Z","y":656},{"x":"2022-12-23T05:26:12.655Z","y":476.0774},{"x":"2022-12-23T05:26:10.579Z","y":464.13437},{"x":"2022-12-23T05:26:08.491Z","y":480.3768},{"x":"2022-12-23T05:26:06.426Z","y":479.91278},{"x":"2022-12-23T05:26:04.345Z","y":491.01263},{"x":"2022-12-23T05:26:02.162Z","y":487.83685},{"x":"2022-12-23T05:26:00.057Z","y":475.14542},{"x":"2022-12-23T04:34:32.020Z","y":479.7275},{"x":"2022-12-23T04:34:29.833Z","y":477.61743}]
```
1. function node output
```auto
[{"series":["A"],"data":[{"1":{"x":"2022-12-23T11:11:10.855Z","y":656},"2":{"x":"2022-12-23T05:26:12.655Z","y":476.0774},"3":{"x":"2022-12-23T05:26:10.579Z","y":464.13437},"4":{"x":"2022-12-23T05:26:08.491Z","y":480.3768},"5":{"x":"2022-12-23T05:26:06.426Z","y":479.91278},"6":{"x":"2022-12-23T05:26:04.345Z","y":491.01263},"7":{"x":"2022-12-23T05:26:02.162Z","y":487.83685},"8":{"x":"2022-12-23T05:26:00.057Z","y":475.14542},"9":{"x":"2022-12-23T04:34:32.020Z","y":479.7275},"10":{"x":"2022-12-23T04:34:29.833Z","y":477.61743}}],"labels":[""]}]
```
1. content of function node.
```auto
var arr = msg.payload; // get the array
var rv = {};// create a new empty object
for (var i = 0; i < arr.length; ++i) {
let key = (i + 1); //built a key
rv[key] = arr[i]; // set value in New object
}
msg.payload = [{
"series": ["A"],
"data": [rv],
"labels": [""]
}]
return msg;
```
please tell me anything required.
---
### Author:  [@E1cid](https://discourse.nodered.org/u/E1cid)
#### Post date: [26 December 2022 09:31 UTC](https://discourse.nodered.org/t/show-postgresql-data-on-chart-in-nodered-dashboard/72706/15 "2022-12-26T09:31:54Z")
I do not see my example function code, but you said you tried it.
Delete your function code and add my example which you said you tried but obviously you did not.
---
### Author:  [@sharad](https://discourse.nodered.org/u/sharad)
#### Post date: [26 December 2022 09:39 UTC](https://discourse.nodered.org/t/show-postgresql-data-on-chart-in-nodered-dashboard/72706/16 "2022-12-26T09:39:14Z")
I tried with your function also. I thought you want my function that's why I send you this.
---
### Author:  [@E1cid](https://discourse.nodered.org/u/E1cid)
#### Post date: [26 December 2022 09:40 UTC](https://discourse.nodered.org/t/show-postgresql-data-on-chart-in-nodered-dashboard/72706/17 "2022-12-26T09:40:31Z")
Then show us the output of the function using my example.
---
### Author:  [@sharad](https://discourse.nodered.org/u/sharad)
#### Post date: [26 December 2022 09:44 UTC](https://discourse.nodered.org/t/show-postgresql-data-on-chart-in-nodered-dashboard/72706/18 "2022-12-26T09:44:30Z")
Yes sure. This flow is with your function but no output shown
```auto
[
{
"id": "a8456abe9dc2476c",
"type": "inject",
"z": "48acf0a74b57fcc9",
"name": "",
"props": [
{
"p": "payload"
},
{
"p": "topic",
"vt": "str"
}
],
"repeat": "",
"crontab": "",
"once": false,
"onceDelay": 0.1,
"topic": "",
"payload": "SELECT \"aqs1_datetime\" x, LAST_VALUE (\"aqs1_eco2\") over(order by \"aqs1_datetime\" desc) y FROM \"air_quality\" where \"aqs1_eco2\" !='NaN' limit 10",
"payloadType": "str",
"x": 230,
"y": 760,
"wires": [
[
"92b824a670efeb1d"
]
]
},
{
"id": "d8c79b20b887df16",
"type": "ui_chart",
"z": "48acf0a74b57fcc9",
"name": "",
"group": "7563cc51495d2ace",
"order": 26,
"width": 22,
"height": 7,
"label": "chart",
"chartType": "line",
"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": 830,
"y": 760,
"wires": [
[]
]
},
{
"id": "92b824a670efeb1d",
"type": "postgres",
"z": "48acf0a74b57fcc9",
"postgresdb": "d91d328a1cfd9505",
"name": "",
"output": true,
"perrow": false,
"rowspermsg": "1",
"return_on_error": false,
"limit_queries": "0",
"limit_by": "payload",
"limit_value": "1",
"limit_drop_intermediate": false,
"limit_drop_if_in_queue": false,
"outputs": true,
"x": 400,
"y": 760,
"wires": [
[
"32d8ed955c608ebd"
]
]
},
{
"id": "32d8ed955c608ebd",
"type": "function",
"z": "48acf0a74b57fcc9",
"name": "function 51",
"func": "msg.payload = [{\n \"series\": [\"A\"],\n \"data\": [msg.payload],\n \"labels\": [\"\"]\n}]\nreturn msg;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 590,
"y": 760,
"wires": [
[
"d8c79b20b887df16",
"be6125e12a05d6b3"
]
]
},
{
"id": "be6125e12a05d6b3",
"type": "debug",
"z": "48acf0a74b57fcc9",
"name": "debug 14",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "false",
"statusVal": "",
"statusType": "auto",
"x": 780,
"y": 700,
"wires": []
},
{
"id": "7563cc51495d2ace",
"type": "ui_group",
"name": "Group 1",
"tab": "2c345b679207408c",
"order": 1,
"disp": false,
"width": "26",
"collapse": false,
"className": ""
},
{
"id": "d91d328a1cfd9505",
"type": "postgresdb",
"cfgname": "",
"hostname": "localhost",
"port": "5432",
"db": "postgres",
"ssl": false
},
{
"id": "2c345b679207408c",
"type": "ui_tab",
"name": "test",
"icon": "dashboard",
"order": 1,
"disabled": false,
"hidden": false
}
]
```
---
### Author:  [@E1cid](https://discourse.nodered.org/u/E1cid)
#### Post date: [26 December 2022 09:50 UTC](https://discourse.nodered.org/t/show-postgresql-data-on-chart-in-nodered-dashboard/72706/19 "2022-12-26T09:50:22Z")
[edit] I see no output from the function node, which i asked to see.
Works for me with simulated db return
```auto
[{"id":"4479f8255f9f6a76","type":"inject","z":"da8a6ef0b3c9a5c8","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"SELECT \"aqs1_datetime\" x, LAST_VALUE (\"aqs1_eco2\") over(order by \"aqs1_datetime\" desc) y FROM \"air_quality\" where \"aqs1_eco2\" !='NaN' limit 10","payloadType":"str","x":310,"y":1120,"wires":[["f62ef5ded5eab12c"]]},{"id":"f62ef5ded5eab12c","type":"change","z":"da8a6ef0b3c9a5c8","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"[{\"x\":\"2022-12-23T11:11:10.855Z\",\"y\":656},{\"x\":\"2022-12-23T05:26:12.655Z\",\"y\":476.0774},{\"x\":\"2022-12-23T05:26:10.579Z\",\"y\":464.13437},{\"x\":\"2022-12-23T05:26:08.491Z\",\"y\":480.3768},{\"x\":\"2022-12-23T05:26:06.426Z\",\"y\":479.91278},{\"x\":\"2022-12-23T05:26:04.345Z\",\"y\":491.01263},{\"x\":\"2022-12-23T05:26:02.162Z\",\"y\":487.83685},{\"x\":\"2022-12-23T05:26:00.057Z\",\"y\":475.14542},{\"x\":\"2022-12-23T04:34:32.020Z\",\"y\":479.7275},{\"x\":\"2022-12-23T04:34:29.833Z\",\"y\":477.61743}]","tot":"json"}],"action":"","property":"","from":"","to":"","reg":false,"x":480,"y":1100,"wires":[["de564af715f02fcb"]]},{"id":"de564af715f02fcb","type":"function","z":"da8a6ef0b3c9a5c8","name":"function 51","func":"msg.payload = [{\n \"series\": [\"A\"],\n \"data\": [msg.payload],\n \"labels\": [\"\"]\n}]\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":670,"y":1100,"wires":[["01df3f76109cb28e","2003c7cc0b6186d4"]]},{"id":"01df3f76109cb28e","type":"ui_chart","z":"da8a6ef0b3c9a5c8","name":"","group":"8adcc559c27febcf","order":26,"width":22,"height":7,"label":"chart","chartType":"line","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":910,"y":1100,"wires":[[]]},{"id":"2003c7cc0b6186d4","type":"debug","z":"da8a6ef0b3c9a5c8","name":"debug 14","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":860,"y":1040,"wires":[]},{"id":"8adcc559c27febcf","type":"ui_group","name":"Group 1","tab":"80b11933687c9cc6","order":1,"disp":false,"width":"26","collapse":false,"className":""},{"id":"80b11933687c9cc6","type":"ui_tab","name":"test","icon":"dashboard","order":1,"disabled":false,"hidden":false}]
```
---
### Author:  [@sharad](https://discourse.nodered.org/u/sharad)
#### Post date: [26 December 2022 09:59 UTC](https://discourse.nodered.org/t/show-postgresql-data-on-chart-in-nodered-dashboard/72706/20 "2022-12-26T09:59:11Z")
yeaahhhhhhh. Now its also worked for me. I have one syntax error that missing ']' thats why not shown out. Thank you so muchhhh. But one more thing please help me that. I want to show date and time in chart. it shows only time
[Next page](https://discourse.nodered.org/t/show-postgresql-data-on-chart-in-nodered-dashboard/72706.md?page=2)