How to combine 2 arrays into 1?

General
1

Dear reader,

I'm a IT student and I'm pretty new to Node-Red. I would like to merge 2 arrays into 1 using a function.

Here is my flow:

[{"id":"49b7aeb2.4e263","type":"tab","label":"Flow 3","disabled":false,"info":""},{"id":"7a43b0e4.382d4","type":"html","z":"49b7aeb2.4e263","name":"","property":"payload","outproperty":"payload","tag":"h3.productrow__title.heading.mb-4.mt-3.mt-md-0","ret":"text","as":"single","x":1040,"y":900,"wires":[["47252756.aeff48"]]},{"id":"7763ca89.b836a4","type":"html","z":"49b7aeb2.4e263","name":"","property":"payload","outproperty":"payload","tag":"span.js-price","ret":"text","as":"single","x":1030,"y":980,"wires":[["eee4ea67.097098"]]},{"id":"9abd892f.a12e08","type":"sqlite","z":"49b7aeb2.4e263","mydb":"bc3f4a1b.b28ee8","sqlquery":"msg.topic","sql":"","name":"","x":1270,"y":160,"wires":[["81bc716d.0bda2"]]},{"id":"47252756.aeff48","type":"join","z":"49b7aeb2.4e263","name":"","mode":"custom","build":"array","property":"payload","propertyType":"msg","key":"payload","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"2","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"num","reduceFixup":"","x":930,"y":580,"wires":[["81bc716d.0bda2","befb27b2.7df558"]]},{"id":"eee4ea67.097098","type":"function","z":"49b7aeb2.4e263","name":"","func":"var ar = [];\nfor(var i = 1; i < msg.payload.length; i += 2) {  // take every second element\n    ar.push(msg.payload[i]);\n    msg.test = ar\n}\nmsg.payload = msg.test\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":780,"y":740,"wires":[["47252756.aeff48"]]},{"id":"befb27b2.7df558","type":"function","z":"49b7aeb2.4e263","name":"","func":"var myArray = msg.payload;\nvar myArray2 = msg.payload[1];\nmsg.topic = `INSERT INTO alarmsysteem (ArtikelNaam,Prijs) VALUES (\"${myArray.join('\"),(\"')}\")`\nvar topic2 = `(\"${myArray2.join('\"),(\"')}\")`\nvar arrayLength = msg.payload.length;\nfor (var i = 0; i <= arrayLength; i++) {\nmsg.testant = arrayLength\n}\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":900,"y":200,"wires":[["81bc716d.0bda2"]]},{"id":"81bc716d.0bda2","type":"debug","z":"49b7aeb2.4e263","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":1080,"y":460,"wires":[]},{"id":"ac54a326.7b805","type":"http request","z":"49b7aeb2.4e263","name":"Request 5MP PoC camera's","method":"GET","ret":"txt","paytoqs":"ignore","url":"https://www.alarmsysteemexpert.nl/nl/hd-camerabewaking/hikvision-turbo-hd-camerabewaking/dome-camera/power-over-coax-poc-cameras/5mp-poc-cameras/","tls":"","persist":false,"proxy":"","authType":"","x":520,"y":1020,"wires":[["7a43b0e4.382d4","7763ca89.b836a4"]]},{"id":"66807acb.21f1b4","type":"inject","z":"49b7aeb2.4e263","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":220,"y":1020,"wires":[["ac54a326.7b805"]]},{"id":"bc3f4a1b.b28ee8","type":"sqlitedb","db":"C:\\sqlite\\alarmsysteemexpert.db","mode":"RW"}]

I'm a new user so I cannot upload any files to clarify my question :frowning:
Anyway I am curious if someone could help me out? :slight_smile:

Kind regards,

Gino

2

If it's simple concatenation, you could use...

var merged = [...array1, ...array2]

If you are wanting to somehow merge values in the arrays, then please clarify.

Ps, you can paste images into the reply to assist your explanation.

1 Like
3

Thank you for you reaction,

image
image427Ă—823 6.81 KB

I want to merge the name and the price in 1 array

4

try...

var merged = [];
var array1 = msg.payload[0];
var array2 = msg.payload[1];
for(let i = 0; i < array1.length; i++) {
  merged.push( { "name": array1[i], "price": array2[i].trim() } );
}
msg.payload = merged;
return msg;
1 Like
5

Hi, I think you sql syntax on Inserting multiple rows is a little off
I made some changes to cleanup the Price since it had a return character in

[{"id":"7a43b0e4.382d4","type":"html","z":"49b7aeb2.4e263","name":"","property":"payload","outproperty":"payload","tag":"h3.productrow__title.heading.mb-4.mt-3.mt-md-0","ret":"text","as":"single","x":770,"y":1060,"wires":[["47252756.aeff48"]]},{"id":"7763ca89.b836a4","type":"html","z":"49b7aeb2.4e263","name":"","property":"payload","outproperty":"payload","tag":"span.js-price","ret":"text","as":"single","x":650,"y":980,"wires":[["eee4ea67.097098"]]},{"id":"47252756.aeff48","type":"join","z":"49b7aeb2.4e263","name":"","mode":"custom","build":"array","property":"payload","propertyType":"msg","key":"payload","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"2","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"num","reduceFixup":"","x":1030,"y":980,"wires":[["81bc716d.0bda2","befb27b2.7df558"]]},{"id":"eee4ea67.097098","type":"function","z":"49b7aeb2.4e263","name":"","func":"var ar = [];\nfor(var i = 1; i < msg.payload.length; i += 2) {  // take every second element\n    ar.push(msg.payload[i].split(\"\\n\")[1].trim());\n    msg.test = ar\n}\nmsg.payload = msg.test\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":800,"y":980,"wires":[["47252756.aeff48"]]},{"id":"befb27b2.7df558","type":"function","z":"49b7aeb2.4e263","name":"","func":"// var myArray = msg.payload;\n// var myArray2 = msg.payload[1];\n// msg.topic = `INSERT INTO alarmsysteem (ArtikelNaam,Prijs) VALUES (\"${myArray.join('\"),(\"')}\")`\n// var topic2 = `(\"${myArray2.join('\"),(\"')}\")`\n// var arrayLength = msg.payload.length;\n// for (var i = 0; i <= arrayLength; i++) {\n// msg.testant = arrayLength\n// }\n// return msg;\n\n\nlet sql = \"INSERT INTO alarmsysteem (ArtikelNaam,Prijs) VALUES \"\n\nlet artikelNaam = msg.payload[0]\nlet prijs = msg.payload[1]\nlet valuesSql = []\n\n\nartikelNaam.forEach( (el, index) => {\n    valuesSql.push(`('${el}', '${prijs[index]}')`)\n    \n})\n\nmsg.topic = sql + valuesSql.join(\",\")\n\nreturn msg","outputs":1,"noerr":0,"initialize":"","finalize":"","x":1220,"y":980,"wires":[["4bc38e10.8c88f8"]]},{"id":"81bc716d.0bda2","type":"debug","z":"49b7aeb2.4e263","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":1150,"y":900,"wires":[]},{"id":"ac54a326.7b805","type":"http request","z":"49b7aeb2.4e263","name":"Request 5MP PoC camera's","method":"GET","ret":"txt","paytoqs":"ignore","url":"https://www.alarmsysteemexpert.nl/nl/hd-camerabewaking/hikvision-turbo-hd-camerabewaking/dome-camera/power-over-coax-poc-cameras/5mp-poc-cameras/","tls":"","persist":false,"proxy":"","authType":"","x":440,"y":1020,"wires":[["7a43b0e4.382d4","7763ca89.b836a4"]]},{"id":"66807acb.21f1b4","type":"inject","z":"49b7aeb2.4e263","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":220,"y":1020,"wires":[["ac54a326.7b805"]]},{"id":"4bc38e10.8c88f8","type":"debug","z":"49b7aeb2.4e263","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":1370,"y":980,"wires":[]}]

Sql should be in the format

INSERT INTO 'tablename' ('column1', 'column2') VALUES
  ('data1', 'data2'),
  ('data3', 'data4'),
  ('data5', 'data6'),
  ('data7', 'data8');

Result after modification in the last function

INSERT INTO alarmsysteem (ArtikelNaam,Prijs) VALUES ('DS-2CE56H0T-ITME, 5MP, 2.8mm, klein model camera, 20m IR, Power over Coax', '62,00'),('DS-2CE56H0T-IT3E, Turbo HD camera Eyeball, 2.8mm, 40m EXIR, Power over Coax', '73,00'),('DS-2CE56H0T-VPITE 5MP D/N IR 3-Axis PoC Vandaal Dome 2.8mm Vaste lens', '82,00'),('5MP, 2.8mm~12mm motorzoom, 40m EXIR, Power over Coax, DS-2CE56H0T-IT3ZE', '119,00'),('5MP, Low Light, motorzoom 2.8~12mm, 40m EXIR, Power over Coax, DS-2CE56H5T-VPIT3ZE', '259,00')
1 Like
6

Thank you for your reactions!! I really appreciate it :slight_smile:
I wonder if you could help me with 1 more question :sweat_smile:
There is a issue where the prices are inserted into the database with a lot of white spaces in front of it. This causes the sqlite db to not read them proberly.

image

image
image579Ă—709 10.6 KB

Maybe one of you guys know how to fix this?

Kind regards,

Gino

7

Thats what the trim() command does. Removes the white space.
String.prototype.trim()

But the price except from the white space that needed to be removed also had a new line character \r.
The following should remove both. Its already in the flow i sent you. Did you import it ? :wink:

 ar.push(msg.payload[i].split("\n")[1].trim());

ps. information on importing flows can be found here

1 Like
8

What is the datatype defined as in the database? If you defined it as a REAL then you'll want to convert the string using parseFloat() after trimming the spaces off it.

2 Likes
9

Sorry I'm not fully awake yet :sweat_smile:

10

Hi Gino,

I was reading a little bit more on the Function node's code for preparing the sql Insert query and there are a few additional steps you have to take in code to make it more 'bulletproof' to failure.

  1. What happens in the case that for some reason the number of ArtikelNaam is not equal to the number of Prijs from the returned http-request ? You should do a check with an if statement before executing further code. if ( artikelNaam.length == prijs.length)

  2. What happens if some ArtikelNaam has in its text single quotes '
    I think this should be handled (escaped) because it could break your sql query as described in this article. maybe you can use a regex replace method to escape the single quotes.

.replace(/\'/g,"''")    // extra single quote for escaping

And this opens the Pandoras box and brings the question .. if we escape the single quote special character, what other special sqlite characters need to be escaped so the query can be sanitized ? :wink:
I think all .. and there's a bunch of them.

So maybe this approach of writing multiple rows of data in one sql query is not so safe and read up on sqlite's prepared statements with parameters (some info on the sqlite node's Help on the sidebar)

  1. @zenofmud made an important point on converting the price to the correct sqlite datatype if this is how your db column is defined.
   ar.push( parseFloat(msg.payload[i].split("\n")[1].trim().replace(",", ".")) );
1 Like
11

I run into a new problem :frowning:.
I want to Scrap the data from another site. There are 3 rows(Firma,ArtikelNaam,Prijs) that I want to fill up. The Firma row has to contain "Jaggsalarm" on every column. The ArtikelNaam and Prijs column have to contain the variable data from <span.price-normal> and <div.name>. I already tried to fix the problem myself but I'm really stuck.
image

[{"id":"91fc0577.a9e628","type":"tab","label":"Flow 6","disabled":false,"info":""},{"id":"86f9e54c.949948","type":"inject","z":"91fc0577.a9e628","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":260,"y":380,"wires":[["c31cc9a8.6f2798"]]},{"id":"c31cc9a8.6f2798","type":"http request","z":"91fc0577.a9e628","name":"Request Hikvision AxPro","method":"GET","ret":"txt","paytoqs":"ignore","url":"https://www.jaggsalarm.nl/hikvision-axpro-alarmsysteem","tls":"","persist":false,"proxy":"","authType":"","x":470,"y":380,"wires":[["d43af523.ebbb08","fa057bda.4c9468"]]},{"id":"d43af523.ebbb08","type":"html","z":"91fc0577.a9e628","name":"","property":"payload","outproperty":"payload","tag":"span.price-normal","ret":"text","as":"single","x":710,"y":340,"wires":[["905e377d.32ff08"]]},{"id":"fa057bda.4c9468","type":"html","z":"91fc0577.a9e628","name":"","property":"payload","outproperty":"payload","tag":"div.name","ret":"text","as":"single","x":680,"y":420,"wires":[["60736d9.b297a94"]]},{"id":"905e377d.32ff08","type":"function","z":"91fc0577.a9e628","name":"","func":"var ar = ;\nfor(var i = 1; i < msg.payload.length; i += 1) { // take every second element\n ar.push(msg.payload[i].split("\n")[1].trim());\n msg.test = ar\n}\nmsg.payload = msg.test\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":900,"y":340,"wires":[["60736d9.b297a94"]]},{"id":"60736d9.b297a94","type":"join","z":"91fc0577.a9e628","name":"","mode":"custom","build":"array","property":"payload","propertyType":"msg","key":"payload","joiner":"\n","joinerType":"str","accumulate":false,"timeout":"","count":"","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"num","reduceFixup":"","x":890,"y":420,"wires":[["7d64efd6.fde5c"]]},{"id":"7d64efd6.fde5c","type":"function","z":"91fc0577.a9e628","name":"","func":"let sql = "INSERT INTO datascraping (Firma,ArtikelNaam,Prijs) VALUES "\n\nlet ArtikelNaam = msg.payload[0]\nlet Prijs = msg.payload[1]\nlet valuesSql = \n\n\nArtikelNaam.forEach( (el, index) => {\n valuesSql.push(('${el}', '${Prijs[index]}'))\n})\n\n\n\nmsg.topic = sql + valuesSql.join(",")\n\n\nreturn msg","outputs":1,"noerr":0,"initialize":"var myArray = msg.payload;\nvar myArray2 = msg.payload[1];\nmsg.topic = INSERT INTO datascraping (ArtikelNaam,Prijs) VALUES (\"${myArray.join('\"),(\"')}\")\nvar topic2 = (\"${myArray2.join('\"),(\"')}\")\nvar arrayLength = msg.payload.length;\nfor (var i = 0; i <= arrayLength; i++) {\nmsg.testant = arrayLength\n}\nreturn msg;","finalize":"","x":1040,"y":420,"wires":[["cf7efb68.6ac098","987fc855.328bd8"]]},{"id":"cf7efb68.6ac098","type":"sqlite","z":"91fc0577.a9e628","mydb":"bc3f4a1b.b28ee8","sqlquery":"msg.topic","sql":"","name":"","x":1250,"y":420,"wires":[]},{"id":"987fc855.328bd8","type":"debug","z":"91fc0577.a9e628","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":1190,"y":480,"wires":},{"id":"bc3f4a1b.b28ee8","type":"sqlitedb","db":"C:\sqlite\datascraping.db","mode":"RW"}]

Maybe someone of you guys can help again? :sweat_smile:

12

Hi, in order to make code more readable and importable it is important to surround your code with three backticks
```
like this
```

You can edit and correct your post by clicking the pencil icon.

See this post for more details - How to share code or flow json

1 Like
13 
[{"id":"91fc0577.a9e628","type":"tab","label":"Flow 6","disabled":false,"info":""},{"id":"86f9e54c.949948","type":"inject","z":"91fc0577.a9e628","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":260,"y":380,"wires":[["c31cc9a8.6f2798"]]},{"id":"c31cc9a8.6f2798","type":"http request","z":"91fc0577.a9e628","name":"Request Hikvision AxPro","method":"GET","ret":"txt","paytoqs":"ignore","url":"https://www.jaggsalarm.nl/hikvision-axpro-alarmsysteem","tls":"","persist":false,"proxy":"","authType":"","x":470,"y":380,"wires":[["d43af523.ebbb08","fa057bda.4c9468"]]},{"id":"d43af523.ebbb08","type":"html","z":"91fc0577.a9e628","name":"","property":"payload","outproperty":"payload","tag":"span.price-normal","ret":"text","as":"single","x":710,"y":340,"wires":[["905e377d.32ff08"]]},{"id":"fa057bda.4c9468","type":"html","z":"91fc0577.a9e628","name":"","property":"payload","outproperty":"payload","tag":"div.name","ret":"text","as":"single","x":680,"y":420,"wires":[["60736d9.b297a94"]]},{"id":"905e377d.32ff08","type":"function","z":"91fc0577.a9e628","name":"","func":"var ar = [];\nfor(var i = 1; i < msg.payload.length; i += 1) {  // take every second element\n    ar.push(msg.payload[i].split(\"\\n\")[1].trim());\n    msg.test = ar\n}\nmsg.payload = msg.test\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":900,"y":340,"wires":[["60736d9.b297a94"]]},{"id":"60736d9.b297a94","type":"join","z":"91fc0577.a9e628","name":"","mode":"custom","build":"array","property":"payload","propertyType":"msg","key":"payload","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"num","reduceFixup":"","x":890,"y":420,"wires":[["7d64efd6.fde5c"]]},{"id":"7d64efd6.fde5c","type":"function","z":"91fc0577.a9e628","name":"","func":"let sql = \"INSERT INTO datascraping (Firma,ArtikelNaam,Prijs) VALUES \"\n\nlet ArtikelNaam = msg.payload[0]\nlet Prijs = msg.payload[1]\nlet valuesSql = []\n\n\nArtikelNaam.forEach( (el, index) => {\n    valuesSql.push(`('${el}', '${Prijs[index]}')`)\n})\n\n\n\nmsg.topic = sql + valuesSql.join(\",\")\n\n\nreturn msg","outputs":1,"noerr":0,"initialize":"var myArray = msg.payload;\nvar myArray2 = msg.payload[1];\nmsg.topic = `INSERT INTO datascraping (ArtikelNaam,Prijs) VALUES (\"${myArray.join('\"),(\"')}\")`\nvar topic2 = `(\"${myArray2.join('\"),(\"')}\")`\nvar arrayLength = msg.payload.length;\nfor (var i = 0; i <= arrayLength; i++) {\nmsg.testant = arrayLength\n}\nreturn msg;","finalize":"","x":1040,"y":420,"wires":[["cf7efb68.6ac098","987fc855.328bd8"]]},{"id":"cf7efb68.6ac098","type":"sqlite","z":"91fc0577.a9e628","mydb":"bc3f4a1b.b28ee8","sqlquery":"msg.topic","sql":"","name":"","x":1250,"y":420,"wires":[[]]},{"id":"987fc855.328bd8","type":"debug","z":"91fc0577.a9e628","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":1190,"y":480,"wires":[]},{"id":"bc3f4a1b.b28ee8","type":"sqlitedb","db":"C:\\sqlite\\datascraping.db","mode":"RW"}]
14

Please don’t ask the same question in multiple threads. Since you opened a new thread. Will close this one