# MySQL to dashboard chart - change function

**URL:** <https://discourse.nodered.org/t/mysql-to-dashboard-chart-change-function/14418>\
**Category:** Dashboard\
**Created:** [17 August 2019 12:16 UTC](https://discourse.nodered.org/t/mysql-to-dashboard-chart-change-function/14418 "2019-08-17T12:16:00Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![EdwardDM10](https://avatars.discourse-cdn.com/v4/letter/e/3d9bf3/32.png) [@EdwardDM10](https://discourse.nodered.org/u/EdwardDM10)\
**Post date:** [17 August 2019 12:16 UTC](https://discourse.nodered.org/t/mysql-to-dashboard-chart-change-function/14418/1 "2019-08-17T12:16:01Z")

</div>

This will be a newbie question, so please bear with me...

I have a MySQL database with a test schema in. I can read and write to it using node red. So far so good.

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

I gather that to get the information in to a chart I have to use the 'change' function.

> [@MySQL Array or Arrays to line chart](https://discourse.nodered.org/t/mysql-array-or-arrays-to-line-chart/12976/7):
>
> Your data is already an array of objects, each with the X (captureTime) and Y (kW Used Today) values to be charted. So the restructuring of the payload can be done with a fairly simple JSONata expression inside a change node -- something like this: [{ "series": ["kW"], "labels": ["kW Used Today"], "data": [[payload.{ "x": captureTime, "y": $."kW Used Today" }]] }]

However, I just can't wrap my head around how to make it work. I have zero background in JS. Would anyway one able to point me in the right direction?

My test schema uses id (int) Value (real) and Timestamp (DT)

My test query brings back just the value and the timestamp, as that is all I want at the moment

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

Any assistance would be much appreciated

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [17 August 2019 13:17 UTC](https://discourse.nodered.org/t/mysql-to-dashboard-chart-change-function/14418/2 "2019-08-17T13:17:02Z")

</div>

connect a change node to the output of the mysql node.  
in the change node select the `j: expression` option (the j: is for jsonata)  
click the three dots on the right of the tab and it will open up.  
paste in the code from Steve's example

```auto
[{
    "series": ["kW"],
    "labels": ["kW Used Today"],
    "data": [[payload.{
        "x": captureTime,
        "y": $."kW Used Today"
    }]]
}]

```

and in your case change `captureTime` to `time` and `kW Used Today` to value and change the series and labels test to what you want.

Then give it a go.

---

<div class="post-metadata">

**Author:** ![EdwardDM10](https://avatars.discourse-cdn.com/v4/letter/e/3d9bf3/32.png) [@EdwardDM10](https://discourse.nodered.org/u/EdwardDM10)\
**Post date:** [17 August 2019 15:00 UTC](https://discourse.nodered.org/t/mysql-to-dashboard-chart-change-function/14418/3 "2019-08-17T15:00:38Z")

</div>

Thanks so much, it works.

I had tried copying that and editing it, but obviously I messed some of the brackets up. Time to get a book!

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

---

<div class="post-metadata">

**Author:** ![shrickus](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/shrickus/32/517_2.png) [@shrickus](https://discourse.nodered.org/u/shrickus)\
**Post date:** [17 August 2019 21:55 UTC](https://discourse.nodered.org/t/mysql-to-dashboard-chart-change-function/14418/4 "2019-08-17T21:55:23Z")

</div>

> [@EdwardDM10](#):
>
> Time to get a book!

Good luck with that! You've wandered into a whole new nascent world of JS Object manipulation, which seems to only be documented online. That being said, you can read the entire set of docs on the `jsonata.org` site in an afternoon -- well worth the price of admission, in my opinion! There is also the [jsonata exerciser](http://try.jsonata.org) page, where you can paste your data, build an expression, and see the results immediately. Of course, that behavior is also built-in to the `change` node, as Paul mentioned above, using the JSONata expression editor panel... Have fun!

---

<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:** [18 August 2019 07:30 UTC](https://discourse.nodered.org/t/mysql-to-dashboard-chart-change-function/14418/5 "2019-08-18T07:30:33Z")

</div>

In fact, if you are a beginner, I suggest not getting too deep in jsonata. It is a very powerful tool for massaging data but it is not the easiest stuff to get to grips with. There is never an absolute requirement to use it, as the same task can always be done using more more straight forward techniques, for example in a function node. My advice would be initially to get to grips with using javascript and worry about whether an in-depth knowledge of jsonata would be useful for a later date.

---

<div class="post-metadata">

**Author:** ![smanjunath211](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/smanjunath211/32/95742_2.png) [@smanjunath211](https://discourse.nodered.org/u/smanjunath211)\
**Post date:** [25 October 2021 12:18 UTC](https://discourse.nodered.org/t/mysql-to-dashboard-chart-change-function/14418/6 "2021-10-25T12:18:21Z")

</div>

after going through this thread nearly 10 times, i finally got it.  
thanks a lot...i almost gave up.

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [27 October 2021 12:09 UTC](https://discourse.nodered.org/t/mysql-to-dashboard-chart-change-function/14418/7 "2021-10-27T12:09:07Z")

</div>

A post was split to a new topic: [How to chart two series in one graph](https://discourse.nodered.org/t/how-to-chart-two-series-in-one-graph/52824)

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [27 October 2021 12:11 UTC](https://discourse.nodered.org/t/mysql-to-dashboard-chart-change-function/14418/8 "2021-10-27T12:11:32Z")

</div>