# MySQL Array or Arrays to line chart

**URL:** <https://discourse.nodered.org/t/mysql-array-or-arrays-to-line-chart/12976>\
**Category:** General\
**Created:** [6 July 2019 15:35 UTC](https://discourse.nodered.org/t/mysql-array-or-arrays-to-line-chart/12976 "2019-07-06T15:35:08Z")\
**Posts on this page:** 19\
**Page:** 1

<div class="post-metadata">

**Author:** ![injector22](https://avatars.discourse-cdn.com/v4/letter/i/e9bcb4/32.png) [@injector22](https://discourse.nodered.org/u/injector22)\
**Post date:** [6 July 2019 15:35 UTC](https://discourse.nodered.org/t/mysql-array-or-arrays-to-line-chart/12976/1 "2019-07-06T15:35:08Z")

</div>

I'm trying to query some data from MySQL and plot it to a line chart. I'm pretty sure the issue is that the output from MySQL is the issue since it outputs an array of arrays which isn't what the chart node accepts.

I'm pretty sure I need to use a function and a loop to iterate through each array, problem is JS is not one of my strengths so i'm kind of lost as to how to accomplish this.

Does anyone have any tips as to how to accomplish this?

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/9/92153a2e708adf469eeb0ce8272222af50fcdcbc.png)

---

<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:** [6 July 2019 15:55 UTC](https://discourse.nodered.org/t/mysql-array-or-arrays-to-line-chart/12976/2 "2019-07-06T15:55:24Z")

</div>

From the info tab of the `ui_chart` node:

> Plots the input values on a chart. This can either be a **time based** line chart, a bar chart (vertical or horizontal), or a pie chart.

(I added the bold) So are you sending in the data in a time based fashion?

Have you tried a search in the [Flows tab](https://flows.nodered.org/?num_pages=1) using a search term of 'chart' to see if any of the examples help you?

---

<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:** [6 July 2019 20:19 UTC](https://discourse.nodered.org/t/mysql-array-or-arrays-to-line-chart/12976/3 "2019-07-06T20:19:50Z")

</div>

This shows you how to format the data if you are trying to fill the chart with pre-existing data.

> <https://github.com/node-red/node-red-dashboard/blob/master/Charts.md>

---

<div class="post-metadata">

**Author:** ![injector22](https://avatars.discourse-cdn.com/v4/letter/i/e9bcb4/32.png) [@injector22](https://discourse.nodered.org/u/injector22)\
**Post date:** [9 July 2019 02:51 UTC](https://discourse.nodered.org/t/mysql-array-or-arrays-to-line-chart/12976/4 "2019-07-09T02:51:45Z")

</div>

I'm familiar with the formatting the data needs in order to get a time based line graph. The issue I have is I don't exactly know enough JS to write a loop of loops to flatten the multiple arrays into a single one to then pass that into the graph node.

Sorry if I wasn't clear.

---

<div class="post-metadata">

**Author:** ![dceejay](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dceejay/32/38_2.png) [@dceejay](https://discourse.nodered.org/u/dceejay)\
**Post date:** [9 July 2019 03:04 UTC](https://discourse.nodered.org/t/mysql-array-or-arrays-to-line-chart/12976/5 "2019-07-09T03:04:11Z")

</div>

First you need to change your query so that it returns pairs of numbers, time and value, then show us the debug of what that array looks like.

---

<div class="post-metadata">

**Author:** ![injector22](https://avatars.discourse-cdn.com/v4/letter/i/e9bcb4/32.png) [@injector22](https://discourse.nodered.org/u/injector22)\
**Post date:** [9 July 2019 13:21 UTC](https://discourse.nodered.org/t/mysql-array-or-arrays-to-line-chart/12976/6 "2019-07-09T13:21:15Z")

</div>

Here's the query with time and value

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/e/eccdf66df9e982f71ca751d58e3aefe43fd17373.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:** [10 July 2019 16:59 UTC](https://discourse.nodered.org/t/mysql-array-or-arrays-to-line-chart/12976/7 "2019-07-10T16:59:56Z")

</div>

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:

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

```

---

<div class="post-metadata">

**Author:** ![injector22](https://avatars.discourse-cdn.com/v4/letter/i/e9bcb4/32.png) [@injector22](https://discourse.nodered.org/u/injector22)\
**Post date:** [10 July 2019 17:07 UTC](https://discourse.nodered.org/t/mysql-array-or-arrays-to-line-chart/12976/8 "2019-07-10T17:07:19Z")

</div>

Wouldn't that just provide a single data point? Don't I have to somehow loop through the parent array and capture each data point?

---

<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:** [10 July 2019 17:32 UTC](https://discourse.nodered.org/t/mysql-array-or-arrays-to-line-chart/12976/9 "2019-07-10T17:32:40Z")

</div>

That's the beauty of JSONata -- the "." operator is a mapping function, that iterates over every object of the payload array.

Since I don't have your query data in a usable format, I couldn't test it -- so your best option is to use the "Expression Tester" built-in to the `change` node... copy that payload array from the debug sidebar, and paste it in after the input property `"payload": ...`

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

As you work on the expression, you can immediately see what the output will be. It's a bit of a learning curve to understand JSONata, but well worth it, imo.

---

<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:** [10 July 2019 17:36 UTC](https://discourse.nodered.org/t/mysql-array-or-arrays-to-line-chart/12976/10 "2019-07-10T17:36:48Z")

</div>

Please note that I changed the syntax of the expression in my previous post to match the working example...

One other comment: you have a HUGE number of rows being returned, for such a little dashboard chart. You will **not** find that UI performance to be very good -- recommended max is less than 1 point per pixel of width, so hundreds, not thousands. Before you go too far you will want to change your sql query to group those readings into larger time slots.

---

<div class="post-metadata">

**Author:** ![injector22](https://avatars.discourse-cdn.com/v4/letter/i/e9bcb4/32.png) [@injector22](https://discourse.nodered.org/u/injector22)\
**Post date:** [10 July 2019 18:09 UTC](https://discourse.nodered.org/t/mysql-array-or-arrays-to-line-chart/12976/11 "2019-07-10T18:09:46Z")

</div>

Well I'll be damned, I had no idea that a JSON expression could do that. I also modified the query to return less objects so now the graph is more readable.

Now I just need to figure out how to make it into a line graph instead of a scatter plot.

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/7/7deef43a4ed6815276847577acd3a653a4aebe10.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:** [10 July 2019 19:28 UTC](https://discourse.nodered.org/t/mysql-array-or-arrays-to-line-chart/12976/12 "2019-07-10T19:28:36Z")

</div>

That "scatter plot" is actually a series of 10 1-point lines (I'm betting). That can happen when your JSONata expression returns the wrong number of nested levels of array data. Maybe I put too many square brackets around that portion of the expression?

---

<div class="post-metadata">

**Author:** ![injector22](https://avatars.discourse-cdn.com/v4/letter/i/e9bcb4/32.png) [@injector22](https://discourse.nodered.org/u/injector22)\
**Post date:** [10 July 2019 21:09 UTC](https://discourse.nodered.org/t/mysql-array-or-arrays-to-line-chart/12976/13 "2019-07-10T21:09:06Z")

</div>

I clearly don't know the formatting well enough. I only see 4 curly brackets and when I remove any pair errors come up.

here's some sample data if that helps:

```auto
[{"captureTime":"2019-07-10T21:06:35.000Z","kW Used Today":285.3500099182129},{"captureTime":"2019-07-10T21:06:05.000Z","kW Used Today":282.60625982284546},{"captureTime":"2019-07-10T21:05:35.000Z","kW Used Today":285.3500099182129},{"captureTime":"2019-07-10T21:05:05.000Z","kW Used Today":285.3500099182129},{"captureTime":"2019-07-10T21:04:35.000Z","kW Used Today":282.60625982284546},{"captureTime":"2019-07-10T21:04:05.000Z","kW Used Today":277.1187596321106},{"captureTime":"2019-07-10T21:03:35.000Z","kW Used Today":277.1187596321106},{"captureTime":"2019-07-10T21:03:05.000Z","kW Used Today":282.60625982284546},{"captureTime":"2019-07-10T21:02:35.000Z","kW Used Today":279.862509727478},{"captureTime":"2019-07-10T21:02:05.000Z","kW Used Today":277.1187596321106}]

```

---

<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:** [10 July 2019 21:20 UTC](https://discourse.nodered.org/t/mysql-array-or-arrays-to-line-chart/12976/14 "2019-07-10T21:20:17Z")

</div>

> [@injector22](#):
>
> I only see 4 curly brackets and when I remove any pair errors come up.

Well, I mentioned that the **square brackets** may be off, not the curly braces...  
Indeed, I think this is what you need:

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

```

P.S. I also added the payload sort syntax `payload^(captureTime)` since the chart expects the x-axis timestamps to be increasing with time.

---

<div class="post-metadata">

**Author:** ![injector22](https://avatars.discourse-cdn.com/v4/letter/i/e9bcb4/32.png) [@injector22](https://discourse.nodered.org/u/injector22)\
**Post date:** [10 July 2019 23:25 UTC](https://discourse.nodered.org/t/mysql-array-or-arrays-to-line-chart/12976/15 "2019-07-10T23:25:55Z")

</div>

That's interesting, for me it's still showing as a "scatter plot". Also for the hell of it I attached a debug node to the chart node and I got this message:

```auto
"Invalid JSONata expression: The expressions within an order-by clause must evaluate to numeric or string values"

```

---

<div class="post-metadata">

**Author:** ![ropske](https://avatars.discourse-cdn.com/v4/letter/r/eb8c5e/32.png) [@ropske](https://discourse.nodered.org/u/ropske)\
**Post date:** [18 February 2020 08:28 UTC](https://discourse.nodered.org/t/mysql-array-or-arrays-to-line-chart/12976/16 "2020-02-18T08:28:34Z")

</div>

Check if your capturetime column in mysql is a string , dont take a date/time column =\> then you get this error message

---

<div class="post-metadata">

**Author:** ![ropske](https://avatars.discourse-cdn.com/v4/letter/r/eb8c5e/32.png) [@ropske](https://discourse.nodered.org/u/ropske)\
**Post date:** [18 February 2020 08:29 UTC](https://discourse.nodered.org/t/mysql-array-or-arrays-to-line-chart/12976/17 "2020-02-18T08:29:14Z")

</div>

I also have a question, the points for my temperature are plotted in the graph, but it doesn't draw a line, anyone knows why?

---

<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:** [18 February 2020 10:06 UTC](https://discourse.nodered.org/t/mysql-array-or-arrays-to-line-chart/12976/18 "2020-02-18T10:06:42Z")

</div>

Why are you replyng to a 7 month old thread?

---

<div class="post-metadata">

**Author:** ![ropske](https://avatars.discourse-cdn.com/v4/letter/r/eb8c5e/32.png) [@ropske](https://discourse.nodered.org/u/ropske)\
**Post date:** [18 February 2020 19:49 UTC](https://discourse.nodered.org/t/mysql-array-or-arrays-to-line-chart/12976/19 "2020-02-18T19:49:41Z")

</div>

Because i just got here to this topic and it helped me and i saw that someone had an error message and i knew the answer, so if anyone else (now or later) check this topic, they know the answer if they got the same error