# Problem getting data from mysql db to line chart

**URL:** <https://discourse.nodered.org/t/problem-getting-data-from-mysql-db-to-line-chart/67973>\
**Category:** General\
**Created:** [20 September 2022 12:51 UTC](https://discourse.nodered.org/t/problem-getting-data-from-mysql-db-to-line-chart/67973 "2022-09-20T12:51:28Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![nuggit](https://avatars.discourse-cdn.com/v4/letter/n/35a633/32.png) [@nuggit](https://discourse.nodered.org/u/nuggit)\
**Post date:** [20 September 2022 12:51 UTC](https://discourse.nodered.org/t/problem-getting-data-from-mysql-db-to-line-chart/67973/1 "2022-09-20T12:51:28Z")

</div>

Hi!

Im a beginner and still learning NodeRed.  
I have browsed through the forums to try to find information.  
However Im stuck.

I have a table in the database that I want to pull some data from.  
each line from the database query looks like this:

time: "2022-09-20T07:58:55.000Z"  
outdoortemperature: 13  
loggedoutdoortemp: 10

What I want the line chart to show  
is how the two temperatures change and corelate over time.

I have created a change node with a J:expression (found that in a tutorial)

This is my J:expression code:

```auto
[{"series" : [$distinct(time)],
    
    "data":$each(payload{time:loggedoutdoortemp},function($val){$val})
   }]

```

I know it probably doesnt make any sense at all but this is the closest I got to something that looks like what I understood is needed in the spec.

However, when I use this in nodered Im getting a  
""Invalid JSONata expression: Key in object structure must evaluate to a string; got: "2022-09-20T07:58:55.000Z"" error message.

And now Im completely stuck.

Any ideas how to get forward?

Thanks for all help and sorry if my post is messy.

/N

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [20 September 2022 13:01 UTC](https://discourse.nodered.org/t/problem-getting-data-from-mysql-db-to-line-chart/67973/2 "2022-09-20T13:01:08Z")

</div>

You could probably adapt this dynamic routine with little ease...

> [@Dynamic series identification from message, send data to Chart](https://discourse.nodered.org/t/dynamic-series-identification-from-message-send-data-to-chart/36682/5):
>
> So, i am not 100% certain what you are after but if (by the title "dynamic series") you mean sending alternative types of data from a database (i.e. varying fields and varying quantities) then this should get you close. It was a simple adaption of the previous solution I pointed you to, modified to build the series from the field names of the 1st data element in the data passed in. There are 2 injects to simulate two different sets of mySQL data [image] [{"id":"227e8b13.87c804…

---

<div class="post-metadata">

**Author:** ![smanjunath211](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/smanjunath211/32/95742_2.png) [@smanjunath211](https://discourse.nodered.org/u/smanjunath211)\
**Post date:** [20 September 2022 13:03 UTC](https://discourse.nodered.org/t/problem-getting-data-from-mysql-db-to-line-chart/67973/3 "2022-09-20T13:03:09Z")

</div>

```auto
[{
    "series": [""],
    "labels": [""],
    "data": [[payload.{
        "x": datetime,
        "y": $."m"
    }]]
}]

```

this works for me.

---

<div class="post-metadata">

**Author:** ![nuggit](https://avatars.discourse-cdn.com/v4/letter/n/35a633/32.png) [@nuggit](https://discourse.nodered.org/u/nuggit)\
**Post date:** [20 September 2022 13:09 UTC](https://discourse.nodered.org/t/problem-getting-data-from-mysql-db-to-line-chart/67973/4 "2022-09-20T13:09:16Z")

</div>

> [@smanjunath211](#):
>
> this works for me.

Hi, yeah, that gave me a list of the timestamps.  
Not sure I follow "where" to add the rest of the data though (temperatures)

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [20 September 2022 14:48 UTC](https://discourse.nodered.org/t/problem-getting-data-from-mysql-db-to-line-chart/67973/5 "2022-09-20T14:48:40Z")

</div>

I take it you want to plot two lines, outdoor and logged?  
If so then the jsonata would be

```auto
(
$lines := ["outdoortemperature","loggedoutdoortemp"];
[
    {
       "series": $lines,
       "data":[
            $lines.[($key := $;
                $$.payload.{"x": $.time, "y": $lookup($, $key)}
                )]
            ],
       "labels":[]
    }
]
)

```

---

<div class="post-metadata">

**Author:** ![nuggit](https://avatars.discourse-cdn.com/v4/letter/n/35a633/32.png) [@nuggit](https://discourse.nodered.org/u/nuggit)\
**Post date:** [20 September 2022 14:58 UTC](https://discourse.nodered.org/t/problem-getting-data-from-mysql-db-to-line-chart/67973/6 "2022-09-20T14:58:28Z")

</div>

YES! Fantastic!  
Thank you very much, worked like a charm!

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [4 October 2022 14:58 UTC](https://discourse.nodered.org/t/problem-getting-data-from-mysql-db-to-line-chart/67973/7 "2022-10-04T14:58:51Z")

</div>

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