How could I display data from a mysql database within a chart?

Here's another flow to demonstrate querying a dB and showing the results using a chart node.

[{"id":"6c770f0a60718c9d","type":"template","z":"1e550c613147af4e","name":"SELECT query","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"SELECT epoch, node_ref, value, sensor FROM ws_sensor_data \nWHERE node_ref = 'node41' \nAND sensor = 'temperature'\nORDER BY epoch ASC\nLIMIT 25","output":"str","x":360,"y":440,"wires":[["475e44d06c39744f"]]},{"id":"8ca3d607de713026","type":"inject","z":"1e550c613147af4e","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":180,"y":440,"wires":[["6c770f0a60718c9d"]]},{"id":"a4c259bb090319aa","type":"ui_chart","z":"1e550c613147af4e","name":"","group":"d6f2dc92ee74295a","order":1,"width":12,"height":8,"label":"Temp readings","chartType":"line","legend":"false","xformat":"HH:mm:ss","interpolate":"linear","nodata":"","dot":false,"ymin":"20","ymax":"60","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":920,"y":440,"wires":[[]]},{"id":"964670b42eaa310a","type":"change","z":"1e550c613147af4e","name":"Report single value","rules":[{"t":"set","p":"payload","pt":"msg","to":"(\t$series := \t    [\t        {\t           \"y_axis\": \"value\",\t           \"label\": \"Temperature\",\t           \"x_axis\": \"epoch\",\t           \"sensor\": \"temperature\"\t        }\t    ];\t$count := $count($series);\t$data := $series.[(\t    $yaxis := $.y_axis;\t    $xaxis := $.x_axis;\t    $$.payload.{\t        \"x\": $lookup($, $xaxis),\t        \"y\": $lookup($, $yaxis)  \t    } \t)];\t\t[\t    {\t        \"series\": $series.label,\t        \"data\": $count > 1 ? [$data] : [[$data]],\t        \"labels\": $series.labels\t    }   \t] \t)","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":710,"y":440,"wires":[["a4c259bb090319aa"]]},{"id":"5487cf6e5bb281a5","type":"ui_button","z":"1e550c613147af4e","name":"","group":"d6f2dc92ee74295a","order":3,"width":3,"height":1,"passthru":false,"label":"Update chart","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"","payloadType":"str","topic":"","topicType":"str","x":170,"y":500,"wires":[["6c770f0a60718c9d"]]},{"id":"59e4f356fb86e531","type":"ui_button","z":"1e550c613147af4e","name":"","group":"d6f2dc92ee74295a","order":5,"width":3,"height":1,"passthru":false,"label":"Clear chart","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"[]","payloadType":"json","topic":"","topicType":"str","x":690,"y":500,"wires":[["a4c259bb090319aa"]]},{"id":"475e44d06c39744f","type":"mysql","z":"1e550c613147af4e","name":"","x":530,"y":440,"wires":[["964670b42eaa310a"]]},{"id":"d6f2dc92ee74295a","type":"ui_group","name":"Chart","tab":"e8284117.b3908","order":2,"disp":true,"width":"12","collapse":false,"className":""},{"id":"e8284117.b3908","type":"ui_tab","name":"mySQL demo","icon":"dashboard","order":23,"disabled":false,"hidden":false}]

This is what the first part of the database table looks like (that I'm using) in this guide...


The first step is to create a dB query to extract the values required. This example will get the last 25 temperature values for 'node41'.

This is what the SELECT query looks like...
single_select_query

The results returned from the MySQL node is an array that needs to be formatted so it matches the needs of the 'chart' node. This is performed by this JSONata code in the 'change' node...

(
$series := 
    [
        {
           "y_axis": "value",
           "label": "Temperature",
           "x_axis": "epoch",
           "sensor": "temperature"
        }
    ];
$count := $count($series);
$data := $series.[(
    $yaxis := $.y_axis;
    $xaxis := $.x_axis;
    $$.payload.{
        "x": $lookup($, $xaxis),
        "y": $lookup($, $yaxis)  
    } 
)];

[
    {
        "series": $series.label,
        "data": $count > 1 ? [$data] : [[$data]],
        "labels": $series.labels
    }   
] 
)

The result on the dashboard should look like this...

The next example is plotting two values (temperature and humidity) for 'node41' against time.

First of all the SELECT query needs to be changed to this...
multi_select_query

Also the contents of the 'change' node needs to be changed as follows...

(
$series := 
    [
        {
           "y_axis": "value",
           "label": "Temperature",
           "x_axis": "epoch",
           "sensor": "temperature"
        },
        {
           "y_axis": "value",
           "label": "Humidity",
           "x_axis": "epoch",
            "sensor": "humidity"
        }
    ];
$count := $count($series);
$data := $series.[(
    $yaxis := $.y_axis;
    $xaxis := $.x_axis;
    $sensor := $.sensor;
    $$.payload[sensor = $sensor].{
        "x": $lookup($, $xaxis),
        "y": $lookup($, $yaxis)  
    } 
)];

[
    {
        "series": $series.label,
        "data": $count > 1 ? [$data] : [[$data]],
        "labels": $series.label
    }   
] 
)

This should produce a result on the dashboard like this...

I hope someone finds this guide useful.

I must thank @E1cid for creating the JSONata code and for encouraging me to produce this guide.

EDIT: Updated SELECT query condition to include parentheses as shown below...
AND (sensor = 'temperature' OR sensor = 'humidity')

1 Like

Wow that's exactly what I was doing, only better, more complete, better illustrated and more timely!

A mysql/mariadb query returns an array of objects, something like this (I'm on my phone, not at a computer so just from memory)
[{id:4, temp:17},{id:5, temp:15.9}]

I was wondering if a query against mongo, influx, mssql, etc returns the same format and so this example works for those databases too.

1 Like

I have no experience with those databases, so cannot comment one way or the other.
I would think it would be a shame if the results-output wasn't similar/compatible.
I'm sure someone with better knowledge than me will jump-in with a definitive answer.

It was useful information. Thank you.

Sorry - I couldn't resist playing a bit more with my flow to demonstrate multiple reports.

Dashboard views...

If I had more spare time I'd look into...

  • creating a single SELECT query and to pass the selection-criteria from the UI buttons
  • changing the Label on the chart to show the name(s) of the reading(s) selected by the button

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