ok .. thats is very clear .. although i dont see a reason for using the json db node .. are you are storing different information with it than what is already in mySQL ?
my recommendation would be to simply query mySQL as shown in my previous post
Although both store the same data but Mysql DB stores this data permanently and json db just has the latest data. The data is requested every minute. The interval cycle to request the data from all the dishes is 1 minute. At any given point of time json db has the last minute data.
Can we build an API using node red by the approach you are suggesting ? querying Mysql ? There is huge data there in Mysql, last two years data plus addition of data everyday.
ofcourse you can .. as i showed above .. you just need to change the SQL command around a bit to just return the last matching row instead of 2 year old data
tell us the db table name and show us a screenshot of the column names and a few rows to get an understanding of the db structure
it could be something like :
let ip = msg.payload.ip;
// replace <table> with your db table
// replace <ip-column> with your ip column name
msg.topic = `SELECT * FROM <table> WHERE <ip-column> = '${ip}' ORDER BY <datatime> DESC LIMIT 1`
return msg;
If you are concerned about performance, by querying the mySQL database to get the last record and prefer to use the json db (deb) node instead .. then wire it, as it would, in the following configuration
Use a Complete msg Debug node after it and copy/paste the complete msg here to see what http-in msg properties are preserved. It could be the case that the deb node replaces the msg.payload.ip with its returned data and that's why it was failing when you tried @E1cid 's example ?
As Nick mentioned, you could get the same information from msg.req.query.ip
Test Flow :
[{"id":"47ecb492.c9102c","type":"http in","z":"223ced0e.462472","name":"","url":"/hello-json","method":"get","upload":false,"swaggerDoc":"","x":260,"y":260,"wires":[["763000fc934138c3"]]},{"id":"2d66c3bc.4e255c","type":"http response","z":"223ced0e.462472","name":"","statusCode":"","headers":{"content-type":"application/json"},"x":770,"y":260,"wires":[]},{"id":"763000fc934138c3","type":"DataOut","z":"223ced0e.462472","collection":"c9de68d4.2e9f18","name":"deb","path":"/","error":true,"x":430,"y":260,"wires":[["e4f767caf4639011","38f079a43a66df1d"]]},{"id":"e4f767caf4639011","type":"debug","z":"223ced0e.462472","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":530,"y":200,"wires":[]},{"id":"38f079a43a66df1d","type":"function","z":"223ced0e.462472","name":"filter","func":"// get the url param from req if payload is destroyed by Deb result\nlet ip = msg.req.query.ip;\n\n\nif (Array.isArray(msg.payload)) {\n msg.payload = msg.payload.filter(el => el.ip === ip)\n}\n\nelse {\n msg.payload = { error: \"No data available\" }\n}\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":590,"y":260,"wires":[["2d66c3bc.4e255c"]]},{"id":"c9de68d4.2e9f18","type":"json-db-collection","name":"deb","collection":"deb","save":true}]
I did as you said and got the following -
PS - I also tried by querying Mysql db as you suggested earlier and it did the trick. But as I said I don't want node red or the react server to query every now and then through the entire historical data as well as the present data to refresh the contents on the React app.
Our React app will be a live page similar to what people have done with the covid data represented making use of covid data related API's and React. Our aim is to get the latest data from the solar field and the dishes to detect any errors the dishes might be encountering or the health status of the dishes.
Thanks.
indeed you are right .. i was thinking about it too when you said thats it was 2 year of data
.. i see that msg.payload is a JSON string
you need to use a JSON node after deb node to make it into a JS Object
[{"id":"47ecb492.c9102c","type":"http in","z":"223ced0e.462472","name":"","url":"/hello-json","method":"get","upload":false,"swaggerDoc":"","x":260,"y":260,"wires":[["763000fc934138c3"]]},{"id":"2d66c3bc.4e255c","type":"http response","z":"223ced0e.462472","name":"","statusCode":"","headers":{"content-type":"application/json"},"x":910,"y":260,"wires":[]},{"id":"763000fc934138c3","type":"DataOut","z":"223ced0e.462472","collection":"c9de68d4.2e9f18","name":"deb","path":"/","error":true,"x":430,"y":260,"wires":[["e4f767caf4639011","10da9c322ec6a1f8"]]},{"id":"e4f767caf4639011","type":"debug","z":"223ced0e.462472","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":530,"y":200,"wires":[]},{"id":"38f079a43a66df1d","type":"function","z":"223ced0e.462472","name":"filter","func":"// get the url param from req if payload is destroyed by Deb result\nlet ip = msg.req.query.ip;\n\n\nif (Array.isArray(msg.payload)) {\n msg.payload = msg.payload.filter(el => el.ip === ip)\n}\n\nelse {\n msg.payload = { error: \"No data available\" }\n}\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":730,"y":260,"wires":[["2d66c3bc.4e255c"]]},{"id":"10da9c322ec6a1f8","type":"json","z":"223ced0e.462472","name":"","property":"payload","action":"","pretty":false,"x":570,"y":260,"wires":[["38f079a43a66df1d"]]},{"id":"c9de68d4.2e9f18","type":"json-db-collection","name":"deb","collection":"deb","save":true}]
Not the reason it failed as msg.payload.ip should exist in my example, as seen in image below.
The reason it failed is that, at that time the unkown deb node the OP added to the example over wrote msg.payload.
And my second example used msg.req.params.json_hello which is also correct as it used the http in node :name convention
I know .. your examples were correct .. i meant, we didnt know how @blob merged the logic from them into his flow with that json db node
@UnborN I tired your flow and it did what was expected. Thanks a lot. 
@E1cid Thank you for your solution also, it's just that I may have not implemented it correctly. From the very beginning of this discussion you were consistently on it.
@knolleary Thank you for taking part in this discussion and providing your valuable inputs. Your participation in this discussion also gave me an opportunity to have some interaction with you. I have always admired you as the mentor/creator of Node Red and I have high regards for you.
Thank you all.
1 Like
This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.
