Node to accept array from SQL

I have a SQL query that returns an array whereby it shows 5 generators (labeled 301, 302, 303, 304 and 305) and a TRUE/FALSE GeneratorIsMapped status for each.

SQLoutput

Every 60 minutes, the SQL query will run. Based on which generators are mapped (where mapped = TRUE), I will perform some testing on those generators for n minutes each.

If 5 generators are mapped, then perform test on each generator lasting 12 minutes per test.
If 4 generators are mapped, then perform test on each generator lasting 15 minutes per test.
If 3 generators are mapped, then perform test on each generator lasting 20 minutes per test.
If 2 generators are mapped, then perform test on each generator lasting 30 minutes per test
If 1 generator is mapped, then perform test lasting 60 minutes.

Which Nodes are best to take the array output from the SQL query and then do the above IF/THEN? I was thinking to use a Template Node that accepts the SQL output and can decide how many of the generators are mapped (1, 2, 3, 4 or 5), followed by a Switch node with 5 outputs.

Here is another view of the output from SQL (taken from the debug pane):

[{"GeneratorNumber":301,"GeneratorIsMapped":"FALSE"},{"GeneratorNumber":302,"GeneratorIsMapped":"FALSE"},{"GeneratorNumber":303,"GeneratorIsMapped":"TRUE"},{"GeneratorNumber":304,"GeneratorIsMapped":"FALSE"},{"GeneratorNumber":305,"GeneratorIsMapped":"TRUE"}]

This could be accomplished with a function node, or change node with a switch node.
i.e.

[{"id":"85b5149e.619a38","type":"inject","z":"57675e72.26a0d8","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"GeneratorNumber\":301,\"GeneratorIsMapped\":\"FALSE\"},{\"GeneratorNumber\":302,\"GeneratorIsMapped\":\"FALSE\"},{\"GeneratorNumber\":303,\"GeneratorIsMapped\":\"TRUE\"},{\"GeneratorNumber\":304,\"GeneratorIsMapped\":\"FALSE\"},{\"GeneratorNumber\":305,\"GeneratorIsMapped\":\"TRUE\"}]","payloadType":"json","x":110,"y":1640,"wires":[["42226113.841b58","187f63ca.2339ac"]]},{"id":"42226113.841b58","type":"change","z":"57675e72.26a0d8","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"$count(payload[*][GeneratorIsMapped='TRUE'])","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":300,"y":1660,"wires":[["893199c5.a9ff5"]]},{"id":"187f63ca.2339ac","type":"function","z":"57675e72.26a0d8","name":"","func":"let count = msg.payload.reduce((a,v) => (v.GeneratorIsMapped === 'TRUE') ? a+1 : a, 0)\nmsg=[];\nmsg[count-1]={payload:count};\nreturn msg;","outputs":5,"noerr":0,"initialize":"","finalize":"","x":310,"y":1760,"wires":[["893199c5.a9ff5"],["7c5f4268.582c7c"],[],[],[]]},{"id":"893199c5.a9ff5","type":"switch","z":"57675e72.26a0d8","name":"","property":"payload","propertyType":"msg","rules":[{"t":"eq","v":"1","vt":"num"},{"t":"eq","v":"2","vt":"num"},{"t":"eq","v":"3","vt":"num"},{"t":"eq","v":"4","vt":"num"},{"t":"eq","v":"5","vt":"num"}],"checkall":"true","repair":false,"outputs":5,"x":520,"y":1660,"wires":[["7c5f4268.582c7c"],["7c5f4268.582c7c"],[],[],[]]},{"id":"7c5f4268.582c7c","type":"debug","z":"57675e72.26a0d8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":550,"y":1440,"wires":[]}]

Thank you. That solution is brilliant. I knew it would be a combination of function, switch and maybe 1 other core node.

just for info-

You could also do it this way and pass the original array for later use, and there is no need for the change node. Just a function node or a switch node.

[{"id":"85b5149e.619a38","type":"inject","z":"57675e72.26a0d8","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"GeneratorNumber\":301,\"GeneratorIsMapped\":\"FALSE\"},{\"GeneratorNumber\":302,\"GeneratorIsMapped\":\"FALSE\"},{\"GeneratorNumber\":303,\"GeneratorIsMapped\":\"TRUE\"},{\"GeneratorNumber\":304,\"GeneratorIsMapped\":\"FALSE\"},{\"GeneratorNumber\":305,\"GeneratorIsMapped\":\"TRUE\"}]","payloadType":"json","x":110,"y":1640,"wires":[["187f63ca.2339ac","893199c5.a9ff5"]]},{"id":"893199c5.a9ff5","type":"switch","z":"57675e72.26a0d8","name":"","property":"$count(payload[*][GeneratorIsMapped='TRUE'])","propertyType":"jsonata","rules":[{"t":"eq","v":"1","vt":"num"},{"t":"eq","v":"2","vt":"num"},{"t":"eq","v":"3","vt":"num"},{"t":"eq","v":"4","vt":"num"},{"t":"eq","v":"5","vt":"num"}],"checkall":"true","repair":false,"outputs":5,"x":320,"y":1640,"wires":[[],["7c5f4268.582c7c"],[],[],[]]},{"id":"187f63ca.2339ac","type":"function","z":"57675e72.26a0d8","name":"","func":"let count = msg.payload.reduce((a,v) => (v.GeneratorIsMapped === 'TRUE') ? a+1 : a, 0)\nlet msg1=[];\nmsg1[count-1]= msg;\nreturn msg1;","outputs":5,"noerr":0,"initialize":"","finalize":"","x":310,"y":1760,"wires":[[],["7c5f4268.582c7c"],[],[],[]]},{"id":"7c5f4268.582c7c","type":"debug","z":"57675e72.26a0d8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":550,"y":1440,"wires":[]}]

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