Array to string with delimiting characters

General
1

Hi, i am stuck at one point and unable to get to solution. I am almost there (or am I?).

i have an array output coming from mysql query, which i need to convert to a string with a semicolon between each string. I can do this manually by using template node with mustache format, but the output from mysql query is dynamic and number of records keep varying based on query condition, so I end up with either less number of mustache template or will get series of extra semicolons. (this is working by the way, since extra semicolons are ignored).
Is there an elegant way to do this ? have given sample input and expected output in inject nodes.

[{"id":"b38fb534d33b53f6","type":"inject","z":"804978a6e5408fa5","name":"Input from MySQL Query","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[[{\"email\":\"someone1@somewhere.com\"},{\"email\":\"someone2@somewhere.com\"},{\"email\":\"someone3@somewhere.com\"},{\"email\":\"someone4@somewhere.com\"},{\"email\":\"someone5@somewhere.com\"},{\"email\":\"someone6@somewhere.com\"},{\"email\":\"someone7@somewhere.com\"}]]","payloadType":"json","x":210,"y":1080,"wires":[["858e43b95512654b"]]},{"id":"d5916c48b28f8265","type":"inject","z":"804978a6e5408fa5","name":"Expected Output","props":[{"p":"to","v":"someone1@somewhere.com;someone2@somewhere.com;someone3@somewhere.com;someone4@somewhere.com;someone5@somewhere.com;someone6@somewhere.com;someone7@somewhere.com","vt":"str"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","x":300,"y":1160,"wires":[["7bc77a280be1270d"]]},{"id":"390780a76666cd4a","type":"debug","z":"804978a6e5408fa5","name":"debug 442","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":870,"y":1080,"wires":[]},{"id":"7bc77a280be1270d","type":"debug","z":"804978a6e5408fa5","name":"debug 443","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"to","targetType":"msg","statusVal":"","statusType":"auto","x":510,"y":1160,"wires":[]},{"id":"b9e4c57df94197e3","type":"change","z":"804978a6e5408fa5","name":"","rules":[{"t":"set","p":"to","pt":"msg","to":"$.payload[*][*].email","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":750,"y":1160,"wires":[["390780a76666cd4a"]]},{"id":"fac2f4c6c23d45fd","type":"split","z":"804978a6e5408fa5","name":"","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","x":570,"y":1040,"wires":[["eb37e0d589a8e012"]]},{"id":"eb37e0d589a8e012","type":"join","z":"804978a6e5408fa5","name":"","mode":"custom","build":"string","property":"payload","propertyType":"msg","key":"topic","joiner":";","joinerType":"str","accumulate":false,"timeout":"","count":"","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":650,"y":1100,"wires":[["b9e4c57df94197e3"]]},{"id":"858e43b95512654b","type":"json","z":"804978a6e5408fa5","name":"","property":"payload","action":"","pretty":true,"x":410,"y":1040,"wires":[["fac2f4c6c23d45fd"]]}]
2

You can split the inner array, then use msg.parts to change the join type
e.g.

[{"id":"b38fb534d33b53f6","type":"inject","z":"d1395164b4eec73e","name":"Input from MySQL Query","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[[{\"email\":\"someone1@somewhere.com\"},{\"email\":\"someone2@somewhere.com\"},{\"email\":\"someone3@somewhere.com\"},{\"email\":\"someone4@somewhere.com\"},{\"email\":\"someone5@somewhere.com\"},{\"email\":\"someone6@somewhere.com\"},{\"email\":\"someone7@somewhere.com\"}]]","payloadType":"json","x":330,"y":1360,"wires":[["cdb293191ed60bc3"]]},{"id":"cdb293191ed60bc3","type":"change","z":"d1395164b4eec73e","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"payload[0]","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":380,"y":1420,"wires":[["a53919dcac6f7afa"]]},{"id":"a53919dcac6f7afa","type":"split","z":"d1395164b4eec73e","name":"","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","x":530,"y":1420,"wires":[["b9e4c57df94197e3"]]},{"id":"b9e4c57df94197e3","type":"change","z":"d1395164b4eec73e","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"payload.email","tot":"msg"},{"t":"set","p":"parts.type","pt":"msg","to":"string","tot":"str"},{"t":"set","p":"parts.ch","pt":"msg","to":";","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":680,"y":1420,"wires":[["eb37e0d589a8e012"]]},{"id":"eb37e0d589a8e012","type":"join","z":"d1395164b4eec73e","name":"","mode":"custom","build":"string","property":"payload","propertyType":"msg","key":"topic","joiner":";","joinerType":"str","accumulate":false,"timeout":"","count":"","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":810,"y":1420,"wires":[["390780a76666cd4a"]]},{"id":"390780a76666cd4a","type":"debug","z":"d1395164b4eec73e","name":"debug 442","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":970,"y":1400,"wires":[]}]
2 Likes
3

No I was no where near.

Thanks for the solution..

4

Missed this line. If extra semicolons are ignored you can also do it direct in a template node
e.g

[{"id":"b38fb534d33b53f6","type":"inject","z":"d1395164b4eec73e","name":"Input from MySQL Query","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[[{\"email\":\"someone1@somewhere.com\"},{\"email\":\"someone2@somewhere.com\"},{\"email\":\"someone3@somewhere.com\"},{\"email\":\"someone4@somewhere.com\"},{\"email\":\"someone5@somewhere.com\"},{\"email\":\"someone6@somewhere.com\"},{\"email\":\"someone7@somewhere.com\"}]]","payloadType":"json","x":330,"y":1360,"wires":[["9652e618c79c91bb"]]},{"id":"9652e618c79c91bb","type":"template","z":"d1395164b4eec73e","name":"","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"{{#payload.0}}{{{email}}};{{/payload.0}}","output":"str","x":540,"y":1360,"wires":[["390780a76666cd4a"]]},{"id":"390780a76666cd4a","type":"debug","z":"d1395164b4eec73e","name":"debug 442","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":870,"y":1360,"wires":[]}]
5

Yes, currently this is exactly my workaround and as i said it is working fine, I wanted an elegant way, which you provided. Surprised though you didn't give me a jsonata solution. :grinning:

6

I thought you wanted a low code option
JSONata

$join($$.payload[0].email, ";")

Javascript

msg.payload = msg.payload[0].map(obj => obj.email).join(";");
return msg;

I thought you mustache template was not dynamic, that's why i added a dynamic template.

1 Like
7

Oh!, I am so sorry. I did not realise the template node you gave was dynamic, its perfect.

Now I have Four ways to acheive the same result. !!! Excellent.

8

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.