SQL Select statement returning object name in the result with Node-Red

[richard.vince]

Noob here just learning about Node-Red.
I have a mysql table with a heap of weather data from my home weather station controlled by a Wemos D1. Its working fine and sending weather data to the Mqqt / Red-node via a JSON string every 30 seconds or so.
I have Red-Node then extracting that JSON string to insert into a MySQL database table.
What, among other things, is I'm trying to return the average temperature readings for the past hour with the following sql query.
SELECT AVG(Temperature) FROM WeatherDB.WeatherData where Dtime > DATE_ADD(Now(), INTERVAL -60 MINUTE);

In MySQL workbench this returns 26.447458, ie just the average number from all the temperature data for the past hour.

But in Node Red the debug returns not only the average data but the select query or part of it as well.
ie. AVG(Temperature): 26.447458

If I output this to a text node for the dashboard using the default for that node (except change the label name) I get on the dashboard the following
[{"AVG(Temperature)":26.565085}]

This looks like a JSON string but if I try to do a JSON.parse and get the figure i get a undefind error.

Am I doing this all wrong? I would like to have a graph or just a text box on the dashboard with average daily rainfall or temp etc.

Regards

Richard.

[jbudd]

Hello @richard.vince and welcome to the forum.

I presume that you are using the most common MySQL node, node-red-node-mysql?

The node returns an array of objects ("object" is more or less synonymous with JSON string).
In your example the array only has one element msg.payload[0] which contains {"AVG(Temperature)":26.565085}

If you feed the SQL output to a debug node you can inspect it's contents. Particularly useful are the two buttons Copy Path and Copy Value.

In this case Copy Value gives me 15.234413038952194 and Copy Path gives me payload[0]["AVG(Temperature)"]

You can if you wish use a Change node to move this path into msg.payload

Note that your SELECT statement could be tweeked to make things a bit easier:

SELECT AVG(Temperature) AS AverageTemperature
FROM WeatherDB.WeatherData 
WHERE Dtime > DATE_ADD(Now(), INTERVAL -60 MINUTE)

(Giving the average value an alias using only alphanumerics means you can access the value with "dot notation": payload[0].AverageTemperature)

[richard.vince]

Thanks,
All fixed. I used your query example as well.
Now just trying to learn how to do a query that returns all the temp data for 24 hours and put it on a chart.

[jbudd]

You might find this thread helpful, though it relates to SQLite not MySQL