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...
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...
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')
