# Saving array to mysql - letters and numbers

**URL:** <https://discourse.nodered.org/t/saving-array-to-mysql-letters-and-numbers/42365>\
**Category:** General\
**Created:** [9 March 2021 21:18 UTC](https://discourse.nodered.org/t/saving-array-to-mysql-letters-and-numbers/42365 "2021-03-09T21:18:38Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![RogierQ](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/rogierq/32/3211_2.png) [@RogierQ](https://discourse.nodered.org/u/RogierQ)\
**Post date:** [9 March 2021 21:18 UTC](https://discourse.nodered.org/t/saving-array-to-mysql-letters-and-numbers/42365/1 "2021-03-09T21:18:38Z")

</div>

Hi!

After about 4 hours googling and trying i hope someone here knows the answer 😉

i have a array that i want to save to a database.  
the array is below  
[2,3,"b",5,6,7]

when it is saved in the table the " " are removed from the b  
tried, escape() jsonparse and so on  
but when i want to convert i back to an array i have errors, or all characters with " "

what is the correct way to do this?  
or is it just not possible?

Thanks  
Rogier

---

<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 March 2021 21:27 UTC](https://discourse.nodered.org/t/saving-array-to-mysql-letters-and-numbers/42365/2 "2021-03-09T21:27:58Z")

</div>

The correct way is really to put each element into a separate field.

Is the array always 6 elements? And is the 3rd item always a char/string?

Or do you really want to save the array as a string? In which case you could convert it to JSON and store it as a string.

The solution changes depending on what you want.

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [9 March 2021 21:36 UTC](https://discourse.nodered.org/t/saving-array-to-mysql-letters-and-numbers/42365/3 "2021-03-09T21:36:22Z")

</div>

What sort of database?  
Usually the quotes shown round a string do not actually exist, they are just used when displaying it to show that the thing between is a string and not a number.

Show us the query you are using to write the data (feed it into a debug node), show us what you get out when you query the database (again in a debug node) and tell us what field types you have configured in the database (if the database is one where you create a table with field types.

---

<div class="post-metadata">

**Author:** ![RogierQ](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/rogierq/32/3211_2.png) [@RogierQ](https://discourse.nodered.org/u/RogierQ)\
**Post date:** [9 March 2021 22:02 UTC](https://discourse.nodered.org/t/saving-array-to-mysql-letters-and-numbers/42365/4 "2021-03-09T22:02:05Z")

</div>

Database is Mariadb

write to

```auto
INSERT INTO `spel`( `data`) VALUES ('1,2,3,b,b,7')

```

query

```auto
[{"id":78,"data":"1,2,3,b,b,7","ts":"2021-03-09T21:57:18.000Z"}]

```

field of data is text field

---

<div class="post-metadata">

**Author:** ![RogierQ](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/rogierq/32/3211_2.png) [@RogierQ](https://discourse.nodered.org/u/RogierQ)\
**Post date:** [9 March 2021 22:05 UTC](https://discourse.nodered.org/t/saving-array-to-mysql-letters-and-numbers/42365/5 "2021-03-09T22:05:46Z")

</div>

Also tried

```auto
"\"b\""

```

That gives me

```auto
1,&quot;b&quot;,4,5,6,7

```

---

<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:** [10 March 2021 00:29 UTC](https://discourse.nodered.org/t/saving-array-to-mysql-letters-and-numbers/42365/6 "2021-03-10T00:29:29Z")

</div>

> [@RogierQ](#):
>
> [2,3,"b",5,6,7]

If you really want to save the array as a string (then restore it later on) then convert the array to JSON before inserting...

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

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

Select data looks like this (after conversion back from JSON to JS)...  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/6/d/6df153e5fbd859a72b6b89353d0ea29ce80b4695.png)

demo flow...

```auto
[{"id":"e96e40f1.fb1b5","type":"debug","z":"47169a4b.e85434","name":"","active":true,"tosidebar":true,"console":false,"tostatus":true,"complete":"topic","targetType":"msg","statusVal":"payload","statusType":"auto","x":892,"y":656,"wires":[]},{"id":"90417dd.11e7f8","type":"debug","z":"47169a4b.e85434","name":"","active":true,"tosidebar":true,"console":false,"tostatus":true,"complete":"payload","targetType":"msg","statusVal":"payload","statusType":"auto","x":1158,"y":608,"wires":[]},{"id":"2663d18c.ed894e","type":"inject","z":"47169a4b.e85434","name":"insert [2,3,\"b\",5,6,7]","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[2,3,\"b\",5,6,7]","payloadType":"json","x":378,"y":608,"wires":[["235f6f6.56b4a9"]]},{"id":"c150d1dc.acaf9","type":"inject","z":"47169a4b.e85434","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":348,"y":864,"wires":[["646d2107.5838d"]]},{"id":"646d2107.5838d","type":"template","z":"47169a4b.e85434","name":"create table jsondata","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"CREATE TABLE `test`.`jsondata` ( \n `id` INT NOT NULL AUTO_INCREMENT , \n `data` VARCHAR(32000) NOT NULL , \n `ts` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP , \n PRIMARY KEY (`id`)\n) ENGINE = MyISAM;\n\n","output":"str","x":532,"y":864,"wires":[["94718358.f209c"]]},{"id":"5c5f83f3.67e3cc","type":"debug","z":"47169a4b.e85434","name":"","active":true,"tosidebar":true,"console":false,"tostatus":true,"complete":"true","targetType":"full","statusVal":"payload","statusType":"auto","x":1138,"y":864,"wires":[]},{"id":"94718358.f209c","type":"mysql","z":"47169a4b.e85434","mydb":"4b1566d2.1f4b38","name":"","x":882,"y":864,"wires":[["5c5f83f3.67e3cc"]]},{"id":"baa7eee5.586d","type":"mysql","z":"47169a4b.e85434","mydb":"4b1566d2.1f4b38","name":"","x":882,"y":608,"wires":[["90417dd.11e7f8"]]},{"id":"535e0efb.bd89f","type":"inject","z":"47169a4b.e85434","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"{\"Word0\":1,\"Word2\":2,\"Word4\":3,\"Word6\":4,\"Word8\":5}","payloadType":"json","x":338,"y":736,"wires":[["89a81ab7.032f78"]]},{"id":"81b016d1.115428","type":"mysql","z":"47169a4b.e85434","mydb":"4b1566d2.1f4b38","name":"","x":770,"y":736,"wires":[["85a9b30f.536e2"]]},{"id":"89a81ab7.032f78","type":"template","z":"47169a4b.e85434","name":"SELECT top 100 * from jsondata","field":"topic","fieldType":"msg","format":"sql","syntax":"mustache","template":"SELECT * from jsondata limit 100;","output":"str","x":562,"y":736,"wires":[["81b016d1.115428"]]},{"id":"7f1d70ee.8bd3d","type":"debug","z":"47169a4b.e85434","name":"","active":true,"tosidebar":true,"console":false,"tostatus":true,"complete":"payload","targetType":"msg","statusVal":"payload","statusType":"auto","x":1158,"y":736,"wires":[]},{"id":"235f6f6.56b4a9","type":"json","z":"47169a4b.e85434","name":"","property":"payload","action":"","pretty":false,"x":562,"y":608,"wires":[["3c68006.976b7"]]},{"id":"3c68006.976b7","type":"template","z":"47169a4b.e85434","name":"build SQL","field":"topic","fieldType":"msg","format":"sql","syntax":"mustache","template":"insert into jsondata (data) values ('{{{payload}}}');","output":"str","x":716,"y":608,"wires":[["baa7eee5.586d","e96e40f1.fb1b5"]]},{"id":"13d8515.bf545af","type":"inject","z":"47169a4b.e85434","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"{\"Word0\":1,\"Word2\":2,\"Word4\":3,\"Word6\":4,\"Word8\":5}","payloadType":"json","x":338,"y":800,"wires":[["5b6bab3d.a2e8e4"]]},{"id":"5b6bab3d.a2e8e4","type":"template","z":"47169a4b.e85434","name":"DELETE from jsondata","field":"topic","fieldType":"msg","format":"sql","syntax":"mustache","template":"DELETE from jsondata;","output":"str","x":532,"y":800,"wires":[["bc70acd9.15ba9"]]},{"id":"bc70acd9.15ba9","type":"mysql","z":"47169a4b.e85434","mydb":"4b1566d2.1f4b38","name":"","x":882,"y":800,"wires":[["6c7dafa1.7408a"]]},{"id":"6c7dafa1.7408a","type":"debug","z":"47169a4b.e85434","name":"","active":true,"tosidebar":true,"console":false,"tostatus":true,"complete":"payload","targetType":"msg","statusVal":"payload","statusType":"auto","x":1158,"y":800,"wires":[]},{"id":"85a9b30f.536e2","type":"function","z":"47169a4b.e85434","name":"convert json to js obj","func":"var rows = msg.payload.map((e) => (\n {\n \"id\":e.id,\n \"data\": JSON.parse(e.data),\n \"ts\":e.ts\n }\n ));\nmsg.payload = rows;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":964,"y":736,"wires":[["7f1d70ee.8bd3d"]]},{"id":"91a73c87.150a3","type":"template","z":"47169a4b.e85434","name":"drop table jsondata","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"DROP TABLE `test`.`jsondata`; ","output":"str","x":522,"y":912,"wires":[["94718358.f209c"]]},{"id":"a25f1553.f00078","type":"inject","z":"47169a4b.e85434","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":348,"y":912,"wires":[["91a73c87.150a3"]]},{"id":"4b1566d2.1f4b38","type":"MySQLdatabase","name":"","host":"192.168.1.38","port":"3306","db":"test","tz":"","charset":"UTF8"}]

```

---

<div class="post-metadata">

**Author:** ![RogierQ](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/rogierq/32/3211_2.png) [@RogierQ](https://discourse.nodered.org/u/RogierQ)\
**Post date:** [10 March 2021 09:06 UTC](https://discourse.nodered.org/t/saving-array-to-mysql-letters-and-numbers/42365/7 "2021-03-10T09:06:59Z")

</div>

Wow how nice that you made an example for me.  
Really happy with that!  
Thanks a lot!!

---

<div class="post-metadata">

**Author:** ![RogierQ](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/rogierq/32/3211_2.png) [@RogierQ](https://discourse.nodered.org/u/RogierQ)\
**Post date:** [10 March 2021 11:25 UTC](https://discourse.nodered.org/t/saving-array-to-mysql-letters-and-numbers/42365/8 "2021-03-10T11:25:32Z")

</div>

Finally found the magic solution...

In the template node it must be with 3 { instead of 2{ .... took me hours be thanks to the example of @Steve-Mcl i found it!

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [9 May 2021 11:26 UTC](https://discourse.nodered.org/t/saving-array-to-mysql-letters-and-numbers/42365/9 "2021-05-09T11:26:02Z")

</div>

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