# How to compare two msg.payload in a same flow

**URL:** <https://discourse.nodered.org/t/how-to-compare-two-msg-payload-in-a-same-flow/26174>\
**Category:** General\
**Created:** [6 May 2020 14:16 UTC](https://discourse.nodered.org/t/how-to-compare-two-msg-payload-in-a-same-flow/26174 "2020-05-06T14:16:28Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![lingzhongli](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/lingzhongli/32/20078_2.png) [@lingzhongli](https://discourse.nodered.org/u/lingzhongli)\
**Post date:** [6 May 2020 14:16 UTC](https://discourse.nodered.org/t/how-to-compare-two-msg-payload-in-a-same-flow/26174/1 "2020-05-06T14:16:28Z")

</div>

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/2/0/20eb160132083584564acc120b906892a69e5443.png)

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](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/9/3/9313f98d8c6e216c70dbfd06d8b7b9cc038dae41.png)

how can i solve this problem?

---

<div class="post-metadata">

**Author:** ![mturnbull](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/mturnbull/32/20742_2.png) [@mturnbull](https://discourse.nodered.org/u/mturnbull)\
**Post date:** [6 May 2020 14:45 UTC](https://discourse.nodered.org/t/how-to-compare-two-msg-payload-in-a-same-flow/26174/2 "2020-05-06T14:45:42Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![lingzhongli](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/lingzhongli/32/20078_2.png) [@lingzhongli](https://discourse.nodered.org/u/lingzhongli)\
**Post date:** [6 May 2020 15:03 UTC](https://discourse.nodered.org/t/how-to-compare-two-msg-payload-in-a-same-flow/26174/3 "2020-05-06T15:03:00Z")

</div>

it can't work..

---

<div class="post-metadata">

**Author:** ![lingzhongli](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/lingzhongli/32/20078_2.png) [@lingzhongli](https://discourse.nodered.org/u/lingzhongli)\
**Post date:** [6 May 2020 15:03 UTC](https://discourse.nodered.org/t/how-to-compare-two-msg-payload-in-a-same-flow/26174/4 "2020-05-06T15:03:52Z")

</div>

```auto
[{"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

---

<div class="post-metadata">

**Author:** ![knolleary](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/knolleary/32/3_2.png) [@knolleary](https://discourse.nodered.org/u/knolleary)\
**Post date:** [6 May 2020 15:06 UTC](https://discourse.nodered.org/t/how-to-compare-two-msg-payload-in-a-same-flow/26174/5 "2020-05-06T15:06:10Z")

</div>

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](https://cookbook.nodered.org/basic/join-streams)

---

<div class="post-metadata">

**Author:** ![mturnbull](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/mturnbull/32/20742_2.png) [@mturnbull](https://discourse.nodered.org/u/mturnbull)\
**Post date:** [7 May 2020 01:19 UTC](https://discourse.nodered.org/t/how-to-compare-two-msg-payload-in-a-same-flow/26174/6 "2020-05-07T01:19:42Z")

</div>

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?

```auto
[{"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"]]}]

```

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [7 May 2020 06:24 UTC](https://discourse.nodered.org/t/how-to-compare-two-msg-payload-in-a-same-flow/26174/7 "2020-05-07T06:24:32Z")

</div>

Please read [how-to-share-code-or-flow-json](https://discourse.nodered.org/t/how-to-share-code-or-flow-json/506)

---

<div class="post-metadata">

**Author:** ![mturnbull](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/mturnbull/32/20742_2.png) [@mturnbull](https://discourse.nodered.org/u/mturnbull)\
**Post date:** [7 May 2020 06:51 UTC](https://discourse.nodered.org/t/how-to-compare-two-msg-payload-in-a-same-flow/26174/8 "2020-05-07T06:51:45Z")

</div>

Thanks Steve,  
Much appreciated... Here we go.

```auto

[{"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"]]}]

```

---

<div class="post-metadata">

**Author:** ![lingzhongli](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/lingzhongli/32/20078_2.png) [@lingzhongli](https://discourse.nodered.org/u/lingzhongli)\
**Post date:** [8 May 2020 09:46 UTC](https://discourse.nodered.org/t/how-to-compare-two-msg-payload-in-a-same-flow/26174/9 "2020-05-08T09:46:13Z")

</div>

Thank you , it help me

But if i compare the data with a string in an array, how can i compare it?

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/b/a/ba8867cb8a90d1ff44677014617f7604e2a61e7f.png)

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/1/b/1b380d004641cabc87b0635250fc9ad3d3a28d69.png)

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?

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [8 May 2020 10:32 UTC](https://discourse.nodered.org/t/how-to-compare-two-msg-payload-in-a-same-flow/26174/10 "2020-05-08T10:32:25Z")

</div>

a quick google search came up with this: [https://www.geeksforgeeks.org/how-to-check-if-a-variable-is-an-array-in-javascript/](https://www.geeksforgeeks.org/how-to-check-if-a-variable-is-an-array-in-javascript/)

---

<div class="post-metadata">

**Author:** ![mturnbull](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/mturnbull/32/20742_2.png) [@mturnbull](https://discourse.nodered.org/u/mturnbull)\
**Post date:** [8 May 2020 12:11 UTC](https://discourse.nodered.org/t/how-to-compare-two-msg-payload-in-a-same-flow/26174/11 "2020-05-08T12:11:52Z")

</div>

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](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/c/d/cd46bcbb435da4b54969650ecccfc75c16787512.jpeg)

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [8 May 2020 12:29 UTC](https://discourse.nodered.org/t/how-to-compare-two-msg-payload-in-a-same-flow/26174/12 "2020-05-08T12:29:12Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![lingzhongli](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/lingzhongli/32/20078_2.png) [@lingzhongli](https://discourse.nodered.org/u/lingzhongli)\
**Post date:** [9 May 2020 07:49 UTC](https://discourse.nodered.org/t/how-to-compare-two-msg-payload-in-a-same-flow/26174/13 "2020-05-09T07:49:54Z")

</div>

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/7/f/7f5b902e7635b3d48d0b7d7bf85eb9617955c193.png)

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

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/3/9/39879febe036804aa4eb705d531295bd80c75423.png)

---

<div class="post-metadata">

**Author:** ![lingzhongli](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/lingzhongli/32/20078_2.png) [@lingzhongli](https://discourse.nodered.org/u/lingzhongli)\
**Post date:** [9 May 2020 07:52 UTC](https://discourse.nodered.org/t/how-to-compare-two-msg-payload-in-a-same-flow/26174/14 "2020-05-09T07:52:00Z")

</div>

yea ... alomst like that

compare with `VOICE` data below

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/3/9/39879febe036804aa4eb705d531295bd80c75423.png)

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [9 May 2020 07:57 UTC](https://discourse.nodered.org/t/how-to-compare-two-msg-payload-in-a-same-flow/26174/15 "2020-05-09T07:57:14Z")

</div>

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

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

> [@Steve-Mcl](#):
>
> 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.

---

<div class="post-metadata">

**Author:** ![lingzhongli](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/lingzhongli/32/20078_2.png) [@lingzhongli](https://discourse.nodered.org/u/lingzhongli)\
**Post date:** [9 May 2020 08:31 UTC](https://discourse.nodered.org/t/how-to-compare-two-msg-payload-in-a-same-flow/26174/16 "2020-05-09T08:31:22Z")

</div>

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/a/3/a3e47e30540cd815b08605754d62f209ef5b4efa.png)

below is the flow

```auto
[{"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

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/6/7/671f1d6f988cbedbc8e62d4147a9ea11f4b85463.png)

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`?

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [9 May 2020 08:34 UTC](https://discourse.nodered.org/t/how-to-compare-two-msg-payload-in-a-same-flow/26174/17 "2020-05-09T08:34:21Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![lingzhongli](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/lingzhongli/32/20078_2.png) [@lingzhongli](https://discourse.nodered.org/u/lingzhongli)\
**Post date:** [9 May 2020 08:49 UTC](https://discourse.nodered.org/t/how-to-compare-two-msg-payload-in-a-same-flow/26174/18 "2020-05-09T08:49:15Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [9 May 2020 09:00 UTC](https://discourse.nodered.org/t/how-to-compare-two-msg-payload-in-a-same-flow/26174/19 "2020-05-09T09:00:28Z")

</div>

> [@lingzhongli](#):
>
> i want compare the `speech to text` result with the voice data in database

So do what i said...

> [@Steve-Mcl](#):
>
> e.g. prepare SQL based on the converted text
> 
> ```auto
> 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.

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?

---

<div class="post-metadata">

**Author:** ![lingzhongli](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/lingzhongli/32/20078_2.png) [@lingzhongli](https://discourse.nodered.org/u/lingzhongli)\
**Post date:** [9 May 2020 09:53 UTC](https://discourse.nodered.org/t/how-to-compare-two-msg-payload-in-a-same-flow/26174/20 "2020-05-09T09:53:32Z")

</div>

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/e/f/efe72562373557345fb7bcbf89e413c214ca133d.png)

like this?

it got error...  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/7/e/7e49f8c2d04be1247220ca740f26856fe8a7c41b.png)

code in function node

```auto
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;

```

[Next page](https://discourse.nodered.org/t/how-to-compare-two-msg-payload-in-a-same-flow/26174.md?page=2)