Search database rows in msg object

[reloopbenny]

Hello!
I have a converted XML in node red and formated to node-red array with JSONata.
I have database same formated output with JSONata.

my xml output in "msg.xmloutput"
and my database output in "msg.dboutput"
I need search my database users name in this xml. If one or more name found in xml I need a send email myself with user details what found in xml file.

Can anyone help me how can I do this in NODE-RED?

[TotallyInformation]

Hi and welcome to the forum.

It is rather hard to give advice when we can't see the format of the data.

In general, there are two approaches to searching JavaScript data. Which you use depends on the format of the data which will either be an array or an object. Objects are easiest to deal with as long os the key (property name) of each object entry matches what you are searching for. Searching an array is harder and you will need to use a map or filter function.

[reloopbenny]

My outputs look like this:
dboutput:

xmloutput:

I need a search every "egyiknev" and "masiknev" value from dboutput in every value of xmloutput "egyiknev" , "masiknev" and "alias" value. If somewhere find a match I need a send email myself with match information(I know how to send email myself from node-red).

[Steve-Mcl]

You realise that is over 4.5 million comparisons? It will not be quick.

(2 * 3 comparisons in 1062 rows against 711 rows == 2 * 3 * 1062 * 711)

How often will you be doing this? Is the data in either source expected to grow over time?

[reloopbenny]

Its runs every day just 1 time.

[Steve-Mcl]

ok, not pretty but here is a way...

[{"id":"bec8666.4ca4798","type":"inject","z":"68fd4d54.10dd54","name":"fake data (1 match)","topic":"","payload":"{\"dboutput\":[{\"ID\":1,\"egyiknev\":\"a1\",\"masiknev\":\"a2\"},{\"ID\":2,\"egyiknev\":\"b1\",\"masiknev\":\"b2\"},{\"ID\":3,\"egyiknev\":\"c1\",\"masiknev\":\"c2\"}],\"xmloutput\":[{\"egyiknev\":\"a7\",\"masiknev\":\"a7\",\"alias\":\"a7\"},{\"egyiknev\":\"b8\",\"masiknev\":\"b8\",\"alias\":\"a8\"},{\"egyiknev\":\"c9\",\"masiknev\":\"c1\",\"alias\":\"c9\"}]}","payloadType":"json","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":630,"y":160,"wires":[["a3f3b8c6.d89278"]]},{"id":"a3f3b8c6.d89278","type":"function","z":"68fd4d54.10dd54","name":"cross search arrays","func":"var xmloutput = msg.payload.xmloutput;\nvar dboutput = msg.payload.dboutput;\nvar found = null;\nfor (let i = 0; i < xmloutput.length; i++) {\n    const d = dboutput[i];\n    for (let j = 0; j < dboutput.length; j++) { \n        const x = xmloutput[j];  \n        if(\n            d.egyiknev == x.egyiknev ||\n            d.egyiknev == x.masiknev ||\n            d.egyiknev == x.alias ||\n            d.masiknev == x.egyiknev ||\n            d.masiknev == x.masiknev ||\n            d.masiknev == x.alias\n        ){\n            found = {d,x};\n            break;\n        }\n    }\n    if(found) break;\n}\nif(found){\n    msg.payload = found;\n    return [msg, null]\n} else {\n   return [null, msg]\n}","outputs":2,"noerr":0,"x":850,"y":200,"wires":[["999e37d7.917cf8"],["7aee6f5c.e9f13"]]},{"id":"999e37d7.917cf8","type":"debug","z":"68fd4d54.10dd54","name":"found","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":1070,"y":180,"wires":[]},{"id":"7aee6f5c.e9f13","type":"debug","z":"68fd4d54.10dd54","name":"not found","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":1080,"y":240,"wires":[]},{"id":"81b5d98d.8b0828","type":"inject","z":"68fd4d54.10dd54","name":"fake data (0 match)","topic":"","payload":"{\"dboutput\":[{\"ID\":1,\"egyiknev\":\"a1\",\"masiknev\":\"a2\"},{\"ID\":2,\"egyiknev\":\"b1\",\"masiknev\":\"b2\"},{\"ID\":3,\"egyiknev\":\"c1\",\"masiknev\":\"c2\"}],\"xmloutput\":[{\"egyiknev\":\"a7\",\"masiknev\":\"a7\",\"alias\":\"a7\"},{\"egyiknev\":\"b8\",\"masiknev\":\"b8\",\"alias\":\"a8\"},{\"egyiknev\":\"c9\",\"masiknev\":\"c9\",\"alias\":\"c9\"}]}","payloadType":"json","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":630,"y":240,"wires":[["a3f3b8c6.d89278"]]}]

Replace the fake inject with real data.

This simulation expects msg.payload to contain 2 properties that are the xmloutput and the dboutput arrays (but you can edit the top to lines of the function to suit)

var xmloutput = msg.payload.xmloutput; //change me to suit
var dboutput = msg.payload.dboutput; //change me to suit

NOTE:

• this solution does not handle un-trimmed strings or case differences (i.e. the values must match EXACTLY)
• this solution will stop and return after the first match found (will need modification if you want all matches)
• this solution expects you to use the payload returned and format in your email template accordingly

[reloopbenny]

I get an error:

I changed the values:

var xmloutput = msg.test.xml.xmloutput;
var dboutput = msg.test.db.db1;

Do you have any idea?

[Steve-Mcl]

@reloopbenny

Do you have any idea?

Yes. What is in those variables? Seems you have an empty row in one of the data arrays.

You might want to add a check in the 2nd function loop after the creation of the const like
if(!d || !x) continue so that it skips empty elements.