How to compare two msg.payload in a same flow

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

image

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"]]}]
1 Like

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?

Capture123

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.

image

from this database in String, but it is in array because it have many data...so i want comapre one by one

yea ... alomst like that

compare with VOICE data below

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...

  • Do you know how to do a SQL statement with a WHERE clause?
  • What are you trying to achieve?
    • Find 1 spoken text in database?

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...

  1. get MIC data

  2. convert to text

  3. 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'"

  4. 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?

like this?

it got error...
image

code in function node

var spokenAsText = msg.payload.trim();// or where ever the speech to text is
var SQL = "SELECT TOP 1 FROM USER WHERE VOICE = '" + spokenAsText + "'";
msg.topic = SQL; //for some nodes - MSSQL uses msg.payload (I dont know what DB you use!
return msg;