Problem getting data from mysql db to line chart

General
1

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:

[{"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

2

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

1 Like
3 
[{
    "series": [""],
    "labels": [""],
    "data": [[payload.{
        "x": datetime,
        "y": $."m"
    }]]
}]

this works for me.

4

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

5

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

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

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

7

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