Dashboard 2 multiple line from sql database

Dashboard
1

I am migrating from Dashboard 1 to Dashboard 2. I select temperatures from a database (mariadb) from a whole day and format those with a JSONata expression in a change-node. Dash1 gives a nice graph with the 3 temperature values from the whole day, but dash2 displays the mysql statement that was used to get the data. Both have the GRAPH node.
Anyone any ideas how to tackle this?
Do you need more info?

2

Yes. SQL query & output data. Picture of Dashboard 1 chart. Picture of Dashboard 2 chart displaying the sql statement.

3

This is the JSONata:

(
  $series := [
    { "field1": "d_waarde", "field2": "d_max", "field3": "d_min", "label": "TuinTemp" }
  ];
  $xaxis := "UNIX_TIMESTAMP(d_ts)";
  [
    {
      "series": ["Huidig","Max","Min"],
      "labels": [$series.label],
      "data": $series.[[
        (
          $yaxis := $.field1;
          $$.payload.{
            "x": $lookup($, $xaxis)*1000,
            "y": $number($lookup($, $yaxis))
          }
        )
      ],[
        (
          $yaxis := $.field2;
          $$.payload.{
            "x": $lookup($, $xaxis)*1000,
            "y": $number($lookup($, $yaxis))
          }
        )
      ],[
        (
          $yaxis := $.field3;
          $$.payload.{
            "x": $lookup($, $xaxis)*1000,
            "y": $number($lookup($, $yaxis))
          }
        )
      ]]
    }
  ]
)
4

SQL Query:

SELECT UNIX_TIMESTAMP(d_ts), d_waarde, d_max, d_min
FROM data
WHERE d_ts >= "{{payload}}:00:00:00"
    AND d_ts <= "{{payload}}:23:59:59"
ORDER  BY d_ts ASC;

Dash1:

Dash1
Dash1335×217 17 KB

Dash2:
Dash2
Dash2384×135 17.4 KB

SELECT UNIX_TIMESTAMP(d_ts), d_waarde, d_max, d_min FROM data WHERE d_ts >= "2022-11-02:00:0... : msg.payload : array[1]
array[1]
0: object
series: array[3]
0: "Huidig"
1: "Max"
2: "Min"
labels: array[1]
0: "TuinTemp"
data: array[3]
0: array[915]
1: array[915]
2: array[915]
5

For dashboard 2 you do not need to process the SQL output to give (x,y) couplets. You should be able to wire directly from the SQL node to the chart.

I would use aliases in the query. I may have misunderstood Huidig/d_waarde since I don't recognise the language.

SELECT UNIX_TIMESTAMP(d_ts) AS ts, d_waarde AS Huidig, d_max AS Max, d_min AS Min ,,,,

Then try configuring the chart like this.

image
image960×169 4.23 KB

If it doesn't work, post some actual SQL output for us to experiment with.

6

The strange thing is, was, that although I cnaged the sql statement the statement in the graph stayed the same. So I deleted the node and created a new one and now it works.
Thanks

7

Dashboard 2 seems to cling to previously displayed data.

If you change a widget config, always reload the dashboard and if that doesn't show the new version, force a reload (ctrl f5 or shift ctrl r or something like that) or clear the browser cache.