from this flow, i want to comapre output of speech to text and DB_MYSQL. But both output show in msg.payload (although i have change the msg.payload of speech to text to transcription by using Transcription, it also will become msg.payload) So, how to do setting in compare data ? that must no be like below
how can i solve this problem?
A few options but perhaps a quick way is to use a change node right after your transcription node and change it’s msg.payload to msg.topic then you can compare msg.topic and msg.payload on your switch node.
it can't work..
[{"id":"98f4cc17.965b","type":"tab","label":"VAES","disabled":false,"info":""},{"id":"ee60c3e3.69e03","type":"mysql","z":"98f4cc17.965b","mydb":"7c738c44.e9a364","name":"DB_MYSQL","x":470,"y":100,"wires":[["df7c1555.276f08"]]},{"id":"5d766314.29f91c","type":"function","z":"98f4cc17.965b","name":"read voice data","func":"msg.topic = \"SELECT VOICE FROM USER\";//data voice from database\nreturn msg; \n\n","outputs":1,"noerr":0,"x":400,"y":340,"wires":[["ee60c3e3.69e03","d97fda3c.851438"]]},{"id":"2b9d8dd1.390142","type":"watson-speech-to-text","z":"98f4cc17.965b","name":"","alternatives":1,"speakerlabels":true,"smartformatting":false,"lang":"en-US","langhidden":"en-US","langcustom":"NoCustomisationSetting","langcustomhidden":"","custom-weight":"0.5","band":"NarrowbandModel","bandhidden":"NarrowbandModel","keywords":"","keywords-threshold":"0.5","word-confidence":false,"password":"","apikey":"aEfplzvlM_B8MA01nsDy3fj6b3CnG6dOQEGISr2gHxxk","payload-response":true,"streaming-mode":false,"streaming-mute":false,"auto-connect":false,"discard-listening":false,"disable-precheck":false,"service-endpoint":"https://api.us-south.speech-to-text.watson.cloud.ibm.com/instances/4ec7115d-1cbf-4ac7-b141-9c9a675fbdac","x":160,"y":340,"wires":[["338d77fb.3e9f88","4428ed4a.f322b4"]]},{"id":"9c343afa.80e438","type":"microphone","z":"98f4cc17.965b","name":"","x":110,"y":240,"wires":[["2b9d8dd1.390142"]]},{"id":"338d77fb.3e9f88","type":"debug","z":"98f4cc17.965b","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"transcription","targetType":"msg","x":370,"y":280,"wires":[]},{"id":"479ecbae.4c1614","type":"mysql","z":"98f4cc17.965b","mydb":"7c738c44.e9a364","name":"DB_MYSQL","x":610,"y":620,"wires":[["418e6c18.88d414"]]},{"id":"f64d53af.b52db","type":"function","z":"98f4cc17.965b","name":"Verify Speech","func":"msg.topic = \"SELECT * FROM USER\";\nreturn msg;","outputs":1,"noerr":0,"x":380,"y":620,"wires":[["479ecbae.4c1614"]]},{"id":"60acba12.c7bd04","type":"inject","z":"98f4cc17.965b","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":180,"y":620,"wires":[["f64d53af.b52db"]]},{"id":"418e6c18.88d414","type":"debug","z":"98f4cc17.965b","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":810,"y":620,"wires":[]},{"id":"ce2be8dd.23cff8","type":"watson-speech-to-text","z":"98f4cc17.965b","name":"","alternatives":1,"speakerlabels":true,"smartformatting":false,"lang":"en-US","langhidden":"en-US","langcustom":"NoCustomisationSetting","langcustomhidden":"","custom-weight":"0.5","band":"NarrowbandModel","bandhidden":"NarrowbandModel","keywords":"","keywords-threshold":"0.5","word-confidence":false,"password":"","apikey":"aEfplzvlM_B8MA01nsDy3fj6b3CnG6dOQEGISr2gHxxk","payload-response":true,"streaming-mode":false,"streaming-mute":false,"auto-connect":false,"discard-listening":false,"disable-precheck":false,"service-endpoint":"https://api.us-south.speech-to-text.watson.cloud.ibm.com/instances/4ec7115d-1cbf-4ac7-b141-9c9a675fbdac","x":480,"y":680,"wires":[["1e12ac7.959fe54"]]},{"id":"a38eb355.0aa43","type":"microphone","z":"98f4cc17.965b","name":"","x":250,"y":680,"wires":[["ce2be8dd.23cff8"]]},{"id":"1e12ac7.959fe54","type":"debug","z":"98f4cc17.965b","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":730,"y":680,"wires":[]},{"id":"31544043.a9475","type":"change","z":"98f4cc17.965b","name":"change to welcome","rules":[{"t":"change","p":"payload","pt":"msg","from":"payload","fromt":"msg","to":"Welcome","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":810,"y":260,"wires":[["8db9d188.50edd"]]},{"id":"d97fda3c.851438","type":"switch","z":"98f4cc17.965b","name":"compare data","property":"topic","propertyType":"msg","rules":[{"t":"eq","v":"payload","vt":"msg"},{"t":"neq","v":"payload","vt":"msg"}],"checkall":"false","repair":false,"outputs":2,"x":640,"y":320,"wires":[["31544043.a9475","e2ee878e.301618"],["fb2abb1b.bb72f8"]]},{"id":"fb2abb1b.bb72f8","type":"change","z":"98f4cc17.965b","name":"change to Access Denied","rules":[{"t":"change","p":"payload","pt":"msg","from":"payload","fromt":"msg","to":"Access Denied","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":810,"y":400,"wires":[["8db9d188.50edd"]]},{"id":"e2ee878e.301618","type":"function","z":"98f4cc17.965b","name":"upload name data","func":"msg.voice = msg.payload;\nmsg.name = msg.transcription;\n//msg.transcription = \"SELECT NAME FROM USER WHERE VOICE = msg.name;” \nmsg.topic= \"INSERT INTO HISTORY (NAME) VALUES( \"+ msg.voice+ \");\"\nreturn msg;","outputs":1,"noerr":0,"x":650,"y":200,"wires":[["ee60c3e3.69e03"]]},{"id":"df7c1555.276f08","type":"json","z":"98f4cc17.965b","name":"","property":"payload","action":"","pretty":false,"x":650,"y":100,"wires":[["b7baa5b9.4f62a8"]]},{"id":"b7baa5b9.4f62a8","type":"debug","z":"98f4cc17.965b","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":830,"y":100,"wires":[]},{"id":"4428ed4a.f322b4","type":"change","z":"98f4cc17.965b","name":"Transcription","rules":[{"t":"change","p":"payload","pt":"msg","from":"payload","fromt":"msg","to":"transcription","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":330,"y":440,"wires":[["5d766314.29f91c"]]},{"id":"8db9d188.50edd","type":"play audio","z":"98f4cc17.965b","name":"Output Sound","voice":"4","x":960,"y":320,"wires":[]},{"id":"7c738c44.e9a364","type":"MySQLdatabase","z":"","host":"165.22.51.44","port":"3306","db":"hireelec_vaes","tz":""}]
this is my flow ...maybe u can help me improve
If you want to compare the values in two separate messages, you first need to join them together into a single message.
The Join node can help you do that. There is an example on the cookbook that may help you: https://cookbook.nodered.org/basic/join-streams
You could try using a join and then compare strings with Jasonata. This is just one of many ways and gets you started with Jasonata, which is a very powerful tool to use in Node-Red.
Here's a sample flow on how that may work. It will produce true or false depending on the result of the comparison. Maybe this could be an idea to look at?
[{"id":"4e88b55c.0c5b9c","type":"tab","label":"Flow 1","disabled":false,"info":""},{"id":"66a036e5.83f9b8","type":"inject","z":"4e88b55c.0c5b9c","name":"","topic":"","payload":"String A","payloadType":"str","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":230,"y":280,"wires":[["195170d6.128d9f"]]},{"id":"bd0cfb82.f14688","type":"inject","z":"4e88b55c.0c5b9c","name":"","topic":"","payload":"String A","payloadType":"str","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":230,"y":220,"wires":[["195170d6.128d9f"]]},{"id":"a0c22c75.03d7","type":"debug","z":"4e88b55c.0c5b9c","name":"Result","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":730,"y":260,"wires":[]},{"id":"195170d6.128d9f","type":"join","z":"4e88b55c.0c5b9c","name":"","mode":"custom","build":"array","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"2","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":410,"y":260,"wires":[["40249274.5bef0c","2107a304.a52f4c"]]},{"id":"40249274.5bef0c","type":"debug","z":"4e88b55c.0c5b9c","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":490,"y":220,"wires":[]},{"id":"2107a304.a52f4c","type":"change","z":"4e88b55c.0c5b9c","name":"compare strings","rules":[{"t":"set","p":"payload","pt":"msg","to":"$string(msg.payload[0]) = $string(msg.payload[1])","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":580,"y":260,"wires":[["a0c22c75.03d7"]]}]
Please read how-to-share-code-or-flow-json
Thanks Steve,
Much appreciated... Here we go.
[{"id":"66a036e5.83f9b8","type":"inject","z":"4e88b55c.0c5b9c","name":"","topic":"","payload":"String A","payloadType":"str","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":390,"y":160,"wires":[["195170d6.128d9f"]]},{"id":"bd0cfb82.f14688","type":"inject","z":"4e88b55c.0c5b9c","name":"","topic":"","payload":"String A","payloadType":"str","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":390,"y":120,"wires":[["195170d6.128d9f"]]},{"id":"a0c22c75.03d7","type":"debug","z":"4e88b55c.0c5b9c","name":"Result","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":850,"y":140,"wires":[]},{"id":"195170d6.128d9f","type":"join","z":"4e88b55c.0c5b9c","name":"","mode":"custom","build":"array","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"2","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":530,"y":140,"wires":[["40249274.5bef0c","2107a304.a52f4c"]]},{"id":"40249274.5bef0c","type":"debug","z":"4e88b55c.0c5b9c","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":570,"y":100,"wires":[]},{"id":"2107a304.a52f4c","type":"change","z":"4e88b55c.0c5b9c","name":"compare strings","rules":[{"t":"set","p":"payload","pt":"msg","to":"$string(msg.payload[0]) = $string(msg.payload[1])","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":700,"y":140,"wires":[["a0c22c75.03d7"]]}]
Thank you , it help me
But if i compare the data with a string in an array, how can i compare it?
in picture above i want compare hello hello hello from speech to text with database ...but in database is in array. how can i compare one by one?
a quick google search came up with this: https://www.geeksforgeeks.org/how-to-check-if-a-variable-is-an-array-in-javascript/
Without seeing what's in your array[10] it's a bit hard to offer an idea.
In the idea I posted earlier, with an attached sample flow, this suggested a Jasonata $string() function comparison to do the job (see below). The result is "true" of "false".
This uses the Jasonata $string() function which automatically "casts" its argument as a string prior to the comparison using the JSON.stringify function - which was one of the reasons why I suggested it.... so depending what's in your array, it may already work.
Otherwise you will need some coding to handle the result of your database query prior to flowing it into the comparison node - which perhaps is more of a Javascript question rather than a Node-Red question?
Isn't this just a simple case of after the speech to text, you perform a database lookup passing the translated speech string as a WHERE clause?
If you get a result, it was found. If you don't, it wasn't.
from this database in String, but it is in array because it have many data...so i want comapre one by one
I dont understand why you would want to get ALL SQL rows and do comparison in JS. Just do a WHERE clause
Do you know how to do a SQL statement with a WHERE clause?
e.g. prepare SQL based on the converted text
var spokenAsText = msg.payload;// or where ever the speech to text is
var SQL = "SELECT TOP 1 FROM table_name WHERE VOICE = '" + spokenAsText + "'";
msg.topic = SQL; //for some nodes - MSSQL uses msg.payload (I dont know what DB you use!
return msg;
... THEN call to the database.
Pretty much as I said ...
below is the flow
[{"id":"43555fcb.61e95","type":"tab","label":"Flow 8","disabled":false,"info":""},{"id":"842ac2b4.3af0a","type":"mysql","z":"43555fcb.61e95","mydb":"7c738c44.e9a364","name":"DB_MYSQL","x":1030,"y":300,"wires":[["f820ef9f.254e7"]]},{"id":"74a54a35.954ce4","type":"watson-speech-to-text","z":"43555fcb.61e95","name":"","alternatives":1,"speakerlabels":true,"smartformatting":true,"lang":"en-US","langhidden":"en-US","langcustom":"NoCustomisationSetting","langcustomhidden":"","custom-weight":"0.5","band":"NarrowbandModel","bandhidden":"NarrowbandModel","keywords":"","keywords-threshold":"0.5","word-confidence":true,"password":"","apikey":"aEfplzvlM_B8MA01nsDy3fj6b3CnG6dOQEGISr2gHxxk","payload-response":true,"streaming-mode":false,"streaming-mute":false,"auto-connect":false,"discard-listening":false,"disable-precheck":false,"service-endpoint":"https://api.us-south.speech-to-text.watson.cloud.ibm.com/instances/4ec7115d-1cbf-4ac7-b141-9c9a675fbdac","x":280,"y":360,"wires":[["a44fd607.9466f8"]]},{"id":"b459f656.3b1138","type":"microphone","z":"43555fcb.61e95","name":"","x":110,"y":360,"wires":[["74a54a35.954ce4"]]},{"id":"8cb32299.134a3","type":"debug","z":"43555fcb.61e95","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":450,"y":280,"wires":[]},{"id":"a3b10137.13e25","type":"mysql","z":"43555fcb.61e95","mydb":"7c738c44.e9a364","name":"DB_MYSQL","x":490,"y":440,"wires":[["df61336.7e497d","b95b74c0.196b28"]]},{"id":"103af1ea.0e058e","type":"inject","z":"43555fcb.61e95","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":100,"y":440,"wires":[["3e2d563e.20b94a"]]},{"id":"15207885.26dfc7","type":"debug","z":"43555fcb.61e95","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":290,"y":520,"wires":[]},{"id":"41d8f9da.5c2498","type":"switch","z":"43555fcb.61e95","name":"true / flase","property":"payload","propertyType":"msg","rules":[{"t":"true"},{"t":"false"}],"checkall":"true","repair":false,"outputs":2,"x":770,"y":460,"wires":[["c5a1b4ed.2825c8","69b8edb2.191f54"],["bcf023c3.faa4a"]]},{"id":"c5a1b4ed.2825c8","type":"function","z":"43555fcb.61e95","name":"upload name data","func":"msg.voice = msg.payload;\nmsg.name = msg.transcription;\n//msg.transcription = \"SELECT NAME FROM USER WHERE VOICE = msg.name;” \nmsg.topic= \"INSERT INTO HISTORY (NAME) VALUES( \"+ msg.voice+ \");\"\nreturn msg;","outputs":1,"noerr":0,"x":1050,"y":400,"wires":[["842ac2b4.3af0a"]]},{"id":"f820ef9f.254e7","type":"json","z":"43555fcb.61e95","name":"","property":"payload","action":"","pretty":false,"x":1190,"y":300,"wires":[["92616f60.2df43"]]},{"id":"92616f60.2df43","type":"debug","z":"43555fcb.61e95","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":1350,"y":300,"wires":[]},{"id":"df61336.7e497d","type":"json","z":"43555fcb.61e95","name":"","property":"payload","action":"","pretty":false,"x":470,"y":520,"wires":[["15207885.26dfc7"]]},{"id":"c53de591.ee9988","type":"debug","z":"43555fcb.61e95","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":650,"y":280,"wires":[]},{"id":"3e2d563e.20b94a","type":"function","z":"43555fcb.61e95","name":"read voice data","func":"msg.topic = \"SELECT VOICE FROM USER\";//data voice from database\nreturn msg; \n\n","outputs":1,"noerr":0,"x":300,"y":440,"wires":[["a3b10137.13e25"]]},{"id":"b95b74c0.196b28","type":"join","z":"43555fcb.61e95","name":"","mode":"custom","build":"array","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"2","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":590,"y":360,"wires":[["c53de591.ee9988","741d3e3f.b6bcf"]]},{"id":"741d3e3f.b6bcf","type":"change","z":"43555fcb.61e95","name":"compare strings","rules":[{"t":"set","p":"payload","pt":"msg","to":"$string(msg.payload[0]) = $string(msg.payload[1])","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":740,"y":360,"wires":[["1f4f2943.2040c7","41d8f9da.5c2498"]]},{"id":"1f4f2943.2040c7","type":"debug","z":"43555fcb.61e95","name":"Result","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":830,"y":280,"wires":[]},{"id":"dd9ee22f.8fbe2","type":"debug","z":"43555fcb.61e95","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":1350,"y":540,"wires":[]},{"id":"69b8edb2.191f54","type":"change","z":"43555fcb.61e95","name":"change true to welcome","rules":[{"t":"change","p":"payload","pt":"msg","from":"true","fromt":"bool","to":"welcome","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":1070,"y":460,"wires":[["c340688.40d3998","dd9ee22f.8fbe2"]]},{"id":"bcf023c3.faa4a","type":"change","z":"43555fcb.61e95","name":"change false to access denied","rules":[{"t":"change","p":"payload","pt":"msg","from":"false","fromt":"bool","to":"access denied","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":1070,"y":540,"wires":[["dd9ee22f.8fbe2","c340688.40d3998"]]},{"id":"c340688.40d3998","type":"play audio","z":"43555fcb.61e95","name":"","voice":"4","x":1330,"y":480,"wires":[]},{"id":"3126e3bc.b9985c","type":"watson-speech-to-text","z":"43555fcb.61e95","name":"","alternatives":"3","speakerlabels":true,"smartformatting":true,"lang":"en-US","langhidden":"en-US","langcustom":"NoCustomisationSetting","langcustomhidden":"","custom-weight":"0.5","band":"NarrowbandModel","bandhidden":"NarrowbandModel","keywords":"","keywords-threshold":"0.5","word-confidence":true,"password":"","apikey":"aEfplzvlM_B8MA01nsDy3fj6b3CnG6dOQEGISr2gHxxk","payload-response":true,"streaming-mode":false,"streaming-mute":false,"auto-connect":false,"discard-listening":false,"disable-precheck":false,"service-endpoint":"https://api.us-south.speech-to-text.watson.cloud.ibm.com/instances/4ec7115d-1cbf-4ac7-b141-9c9a675fbdac","x":480,"y":980,"wires":[["627d8e3.4f0947"]]},{"id":"462b985a.b52748","type":"microphone","z":"43555fcb.61e95","name":"","x":290,"y":980,"wires":[["3126e3bc.b9985c"]]},{"id":"627d8e3.4f0947","type":"debug","z":"43555fcb.61e95","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":690,"y":980,"wires":[]},{"id":"a44fd607.9466f8","type":"function","z":"43555fcb.61e95","name":"trim()","func":"msg.payload = msg.payload.trim();\nreturn msg;","outputs":1,"noerr":0,"x":450,"y":360,"wires":[["8cb32299.134a3","b95b74c0.196b28"]]},{"id":"7c738c44.e9a364","type":"MySQLdatabase","z":"","host":"165.22.51.44","port":"3306","db":"hireelec_vaes","tz":""}]
this is the database
if using this way, the flow like need change...because can't use join already...
the speech to text like need link to read voice data?
Please answer questions...
i dont know how to do the SQL statement with where
i want compare the speech to text result with the voice data in database...but in my flow, Result node is show the database in array...the it cannot comapre with speech to text that with only sentence... so, it need compare one by one...
So do what i said...
MIC --> Speech to text --> Function node (as above - to generate a SQL with WHERE clause) --> done!
How it should work...
get MIC data
convert to text
generate a SQL statement (with a WHERE clause set to the converted text) and pass it out in msg.topic to the DATABASE node
e.g. SELECT TOP 1 * from MY_TABLE WHERE VOICE = 'hello hello hello'"
DATABASE node gets only 1 row - IF FOUND! (otherwise you get nothing back from the database meaning is WASNT FOUND)
This means there is NO NEED to search for text in node-red - SQL does that through the WHERE clause. Delete the JOIN node, and comparison methods - they are not necessary.
Do you understand?
Copyright OpenJS Foundation and Node-RED contributors. All rights reserved. The OpenJS Foundation has registered trademarks and uses trademarks. For a list of trademarks of the OpenJS Foundation, please see our Trademark Policy and Trademark List. Trademarks and logos not indicated on the list of OpenJS Foundation trademarks are trademarks™ or registered® trademarks of their respective holders. Use of them does not imply any affiliation with or endorsement by them.
The OpenJS Foundation | Terms of Use | Privacy Policy | OpenJS Foundation Bylaws | Trademark Policy | Trademark List | Cookie Policy
