MySQL to dashboard chart - change function

Dashboard
1

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
nodered1303Ă—412 21.1 KB

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

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
image318Ă—504 9.07 KB

Any assistance would be much appreciated

2

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

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

3 Likes
3

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
image.png1802Ă—785 48.9 KB

1 Like
4

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 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!

5

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.

1 Like
6

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

1 Like
7

A post was split to a new topic: How to chart two series in one graph