Back story:
I am running node-red on my RPi which gathers data from and Arduino and stores that data in an SQLite database via a Perl script. Works great! I thought I would give node-red a shot and managed to get a couple of gauges working, and a couple of text displays as well. Took a while, but that is fine.
What I Want..:
I am using both Dashboard and the SQLite modules. What I want to do is show some number (perhaps several hundred) of values on a line chart. So I run a query that goes something like:
select time_stamp, temp, temp_c from weather_data where time_stamp >= datetime('now', '-24 hour') order by time_stamp
All is well, the query runs fine and a rather large nested object is returned.
What I Don't Know:
What I have not figured out is how to give the chart what it wants from the SQLite sourced returned object. My javascript skillset is not terrible, but not great either. Thus, I am looking for an example which uses these two specific items together. (sqlite and dashboard chart). Does anyone know of or have an example that I might model after? I simply do not know how to coerce what is delivered from the sqlite db into what the dashboard line chart wants.
I have searched for this on the web and, either I don't know enough to frame the question correctly, or there are not many folks that are doing this. I am very new to node-red also (about three days now), so I apologize if my question is covered somewhere. Any help appreciated...
To be pedantic what you should get back from the sql node is an array of objects, rather than a large nested object. So having got the data where it came from is now irrelevant, so we can forget about sqlite.
If you look in the Info tab for the chart you will see a link pointing to where you can find the details of how to pre-format data to be passed a complete set to the chart. Have a look at that. Then you need to format the data you have according to the requirements there. Ask again if you still have problems, telling us what you have tried. I suggest initially using a query which just gives you three or four points. Then you can feed that into a debug node (as you have done already) and make sure you understand that format. Then following whatever is needed to re-format it you can feed it into another debug node to see what is coming out. When that is right then feed it into the chart.
The problem I face with the chart is due to my lack of knowledge of JS. I learn by example, and thus, if I see a working example of code which coerces the results from a SQLite query node into a single time-series line chart, I am fairly certain I can take off from there. Further searches for such example code have yielded nothing, which I find rather odd as it seems a rather obvious application of the two nodes.
After working on this on and off for the last week, I still have yet to get my data into the format required in the chart documentation (that pointed out by Colin). At this point, I am frustrated enough to simply write a Perl script and see if I can stream that to the chart in some way. But if I do that, I really don't need Node-Red at all.
To summarize what I am (still) looking for:
An example (function?) node which coerces the output of an SQLite node (single series with ISO8601 timestamps) into the input which is required by the Dashboard (line chart).
The fact that it is coming from sqlite is irrelevant. I presume that from the sqlite node you are getting an array of records containing a timestamp and a value. Is that right? Show us the format of one of the elements of the array (from a debug node) so we can see what the elements are called.
Then we can work out how to massage it into the format described for line charts in https://github.com/node-red/node-red-dashboard/blob/master/Charts.md
A quick search for "line chart" on this forum returns this recent discussion with examples on transforming the data using both a JS function and the JSONata expression evaluator.
As Colin says, posting a sample of your query results, and the current flow you are working on, will generally get better responses -- since we all like tweaking flows and code, and not so much spending time generating sample data or flows from a textual description...
