# Insert array of json data in mariadb

**URL:** <https://discourse.nodered.org/t/insert-array-of-json-data-in-mariadb/3776>\
**Category:** General\
**Created:** [8 October 2018 09:29 UTC](https://discourse.nodered.org/t/insert-array-of-json-data-in-mariadb/3776 "2018-10-08T09:29:19Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![tramanah](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/tramanah/32/9122_2.png) [@tramanah](https://discourse.nodered.org/u/tramanah)\
**Post date:** [8 October 2018 09:29 UTC](https://discourse.nodered.org/t/insert-array-of-json-data-in-mariadb/3776/1 "2018-10-08T09:29:19Z")

</div>

msg.topic = "Insert into mytable(DeviceId,Line,outPIN,count,Time) values (?,?,?,?,?)";  
msg.payload = [{"DeviceId": 1,  
"Line": 1,  
"outPIN": "GPIO1",  
"count": 15,  
"Time": "2018-09-23 08:14:45"},  
{"DeviceId": 1,  
"Line": 1,  
"outPIN": "GPIO1",  
"count": 15,  
"Time": "2018-09-23 08:15:13"},  
{"DeviceId": 1,  
"Line": 1,  
"outPIN": "GPIO1",  
"count": 15,  
"Time": "2018-09-23 10:45:36"},  
{"DeviceId": 1,  
"Line": 1,  
"outPIN": "GPIO1",  
"count": 15,  
"Time": "2018-09-23 10:48:51"}]  
return msg;

Error message : msg : error

"Error: ER\_PARSE\_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '?)' at line 1"

---

<div class="post-metadata">

**Author:** ![ukmoose](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/ukmoose/32/13_2.png) [@ukmoose](https://discourse.nodered.org/u/ukmoose)\
**Post date:** [8 October 2018 09:45 UTC](https://discourse.nodered.org/t/insert-array-of-json-data-in-mariadb/3776/2 "2018-10-08T09:45:20Z")

</div>

Is this a duplicate of your previous post?

---

<div class="post-metadata">

**Author:** ![tramanah](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/tramanah/32/9122_2.png) [@tramanah](https://discourse.nodered.org/u/tramanah)\
**Post date:** [8 October 2018 10:45 UTC](https://discourse.nodered.org/t/insert-array-of-json-data-in-mariadb/3776/3 "2018-10-08T10:45:48Z")

</div>

Initially i try with single array set of data. Now i am trying with set of array data.

---

<div class="post-metadata">

**Author:** ![ghayne](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/ghayne/32/39_2.png) [@ghayne](https://discourse.nodered.org/u/ghayne)\
**Post date:** [8 October 2018 11:09 UTC](https://discourse.nodered.org/t/insert-array-of-json-data-in-mariadb/3776/4 "2018-10-08T11:09:00Z")

</div>

> [@tramanah](#):
>
> ,?)";

Should be ,);"

---

<div class="post-metadata">

**Author:** ![tramanah](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/tramanah/32/9122_2.png) [@tramanah](https://discourse.nodered.org/u/tramanah)\
**Post date:** [8 October 2018 11:17 UTC](https://discourse.nodered.org/t/insert-array-of-json-data-in-mariadb/3776/5 "2018-10-08T11:17:03Z")

</div>

msg.topic = "Insert into mytable(DeviceId,Line,outPIN,count,Time) values (?,?,?,?,?,);"

"Error: ER\_PARSE\_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '?)' at line 1"

---

<div class="post-metadata">

**Author:** ![ghayne](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/ghayne/32/39_2.png) [@ghayne](https://discourse.nodered.org/u/ghayne)\
**Post date:** [8 October 2018 11:25 UTC](https://discourse.nodered.org/t/insert-array-of-json-data-in-mariadb/3776/6 "2018-10-08T11:25:41Z")

</div>

One comma too many inside the brackets!

---

<div class="post-metadata">

**Author:** ![tramanah](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/tramanah/32/9122_2.png) [@tramanah](https://discourse.nodered.org/u/tramanah)\
**Post date:** [8 October 2018 11:29 UTC](https://discourse.nodered.org/t/insert-array-of-json-data-in-mariadb/3776/7 "2018-10-08T11:29:32Z")

</div>

> [@tramanah](#):
>
> msg.topic = "Insert into mytable(DeviceId,Line,outPIN,count,Time) values (,);"

Still error persist.

---

<div class="post-metadata">

**Author:** ![ghayne](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/ghayne/32/39_2.png) [@ghayne](https://discourse.nodered.org/u/ghayne)\
**Post date:** [8 October 2018 11:32 UTC](https://discourse.nodered.org/t/insert-array-of-json-data-in-mariadb/3776/8 "2018-10-08T11:32:26Z")

</div>

> [@tramanah](#):
>
> msg.topic = "Insert into mytable(DeviceId,Line,outPIN,count,Time) values (?,?,?,?,?,);"

msg.topic = "Insert into mytable(DeviceId,Line,outPIN,count,Time) values (?,?,?,?,?);"

---

<div class="post-metadata">

**Author:** ![tramanah](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/tramanah/32/9122_2.png) [@tramanah](https://discourse.nodered.org/u/tramanah)\
**Post date:** [8 October 2018 11:36 UTC](https://discourse.nodered.org/t/insert-array-of-json-data-in-mariadb/3776/9 "2018-10-08T11:36:34Z")

</div>

msg.topic = "Insert into mytable1(DeviceId,Line,outPIN,count,Time) values (?,?,?,?,?);"

"Error: ER\_PARSE\_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '?)' at line 1"

Just for info on mytable1:  
CREATE TABLE `mytable1` (  
`ID` INT(11) NOT NULL AUTO\_INCREMENT,  
`DeviceId` INT(5) NULL DEFAULT NULL,  
`Line` INT(5) NULL DEFAULT NULL,  
`outPIN` VARCHAR(10) NULL DEFAULT NULL COMMENT 'GPIO',  
`count` INT(5) NULL DEFAULT NULL,  
`Time` TIMESTAMP NOT NULL DEFAULT '',  
PRIMARY KEY (`ID`)  
)  
COLLATE='latin1\_swedish\_ci'  
ENGINE=InnoDB  
AUTO\_INCREMENT=37  
;

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [8 October 2018 14:24 UTC](https://discourse.nodered.org/t/insert-array-of-json-data-in-mariadb/3776/10 "2018-10-08T14:24:32Z")

</div>

I would suggest you step back and make sure you can do an insert with actual values before moving onto the ? marks and use the values from the forst element in the array.

---

<div class="post-metadata">

**Author:** ![tramanah](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/tramanah/32/9122_2.png) [@tramanah](https://discourse.nodered.org/u/tramanah)\
**Post date:** [9 October 2018 08:44 UTC](https://discourse.nodered.org/t/insert-array-of-json-data-in-mariadb/3776/11 "2018-10-09T08:44:42Z")

</div>

msg.topic = "Insert into mytable1 (DeviceId,Line,outPIN,count,Time) values ( '1','1', 'GPIO1','15','2018-09-23 08:14:45')";

Its works only on this format

---

<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 October 2018 09:08 UTC](https://discourse.nodered.org/t/insert-array-of-json-data-in-mariadb/3776/12 "2018-10-09T09:08:24Z")

</div>

Does it work in the form you posted in a different thread, with question marks and a simple array of values?

---

<div class="post-metadata">

**Author:** ![tramanah](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/tramanah/32/9122_2.png) [@tramanah](https://discourse.nodered.org/u/tramanah)\
**Post date:** [9 October 2018 09:10 UTC](https://discourse.nodered.org/t/insert-array-of-json-data-in-mariadb/3776/13 "2018-10-09T09:10:03Z")

</div>

Nope, doesn't work.

---

<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 October 2018 09:43 UTC](https://discourse.nodered.org/t/insert-array-of-json-data-in-mariadb/3776/14 "2018-10-09T09:43:30Z")

</div>

Look in the mariadb log to see exactly what query it is being given.

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [9 October 2018 10:09 UTC](https://discourse.nodered.org/t/insert-array-of-json-data-in-mariadb/3776/15 "2018-10-09T10:09:48Z")

</div>

Take a look at [https://www.w3schools.com/nodejs/nodejs\_mysql\_insert.asp](https://www.w3schools.com/nodejs/nodejs_mysql_insert.asp) the section " Insert Multiple Records"

In it the data is an array of arrays ie instead of [{...},{...}] they use [[...],[...]] then only use one ? in the insert

---

<div class="post-metadata">

**Author:** ![knolleary](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/knolleary/32/3_2.png) [@knolleary](https://discourse.nodered.org/u/knolleary)\
**Post date:** [9 October 2018 14:41 UTC](https://discourse.nodered.org/t/insert-array-of-json-data-in-mariadb/3776/16 "2018-10-09T14:41:06Z")

</div>

Which node-red node are you using to insert into mariadb? Does it even support passing in a array of data to do multiple inserts? I'm not aware of any node that does support doing that.

---

<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 October 2018 15:03 UTC](https://discourse.nodered.org/t/insert-array-of-json-data-in-mariadb/3776/17 "2018-10-09T15:03:11Z")

</div>

> [@tramanah](#):
>
> Nope, doesn't work.

Can I just check that you mean you cannot insert one row by passing in an array of values in the payload such as `['1','1', 'GPIO1','15','2018-09-23 08:14:45']`. If so then as I suggested look in the mariadb log, but also give us the information @knolleary has asked for.

---

<div class="post-metadata">

**Author:** ![tramanah](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/tramanah/32/9122_2.png) [@tramanah](https://discourse.nodered.org/u/tramanah)\
**Post date:** [12 October 2018 04:12 UTC](https://discourse.nodered.org/t/insert-array-of-json-data-in-mariadb/3776/18 "2018-10-12T04:12:28Z")

</div>

Its **doesn't work** on question mark and **work** in this format  
as `['1','1', 'GPIO1','15','2018-09-23 08:14:45']`

---

<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:** [12 October 2018 08:02 UTC](https://discourse.nodered.org/t/insert-array-of-json-data-in-mariadb/3776/19 "2018-10-12T08:02:24Z")

</div>

Please try and answer questions asked, it is difficult to help if you don't, you have not answered @knolleary's question as to which node you are using to access the data (possible one of the node-red-contrib nodes). Also you have not said what you see in the mariadb log, which can show you exactly the query that is failing.

---

<div class="post-metadata">

**Author:** ![tramanah](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/tramanah/32/9122_2.png) [@tramanah](https://discourse.nodered.org/u/tramanah)\
**Post date:** [28 October 2018 13:30 UTC](https://discourse.nodered.org/t/insert-array-of-json-data-in-mariadb/3776/20 "2018-10-28T13:30:32Z")

</div>

Hi Members, sorry for silence. I do manage to resolve the issue. Basically i didn't understand how the mariadb works with json data type format.

MariaDB starting with version [10.2.7](https://mariadb.com/kb/en/mariadb-1027-release-notes/).

> **[JSON Data Type](https://mariadb.com/kb/en/library/json-data-type/)**
>
> Compatibility data type that is an alias for LONGTEXT.

With attached link the shows clearly the json data type works in single column of the table. We can have many columns if needed.

Thank you for supports