# JSON array to MySQL

**URL:** <https://discourse.nodered.org/t/json-array-to-mysql/14597>\
**Category:** General\
**Created:** [21 August 2019 11:11 UTC](https://discourse.nodered.org/t/json-array-to-mysql/14597 "2019-08-21T11:11:21Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![hobojoe720](https://avatars.discourse-cdn.com/v4/letter/h/76d3ee/32.png) [@hobojoe720](https://discourse.nodered.org/u/hobojoe720)\
**Post date:** [21 August 2019 11:11 UTC](https://discourse.nodered.org/t/json-array-to-mysql/14597/1 "2019-08-21T11:11:21Z")

</div>

Hi guys,

Been hunting the forums for an answer and cant find one so annoyingly I have to ask:  
I have a flow that pulls a list of results from an API. This works. It then cleans up the JSON to only include these fields (thanks to these forums).

The next step is to store this into a MySQL database. All the examples and issues I can find on the forums are for where there is a single entry to be inserted (e.g. in the JSON below, if I was just inserting count.

Using the mysql node, I get that I need to add my MySQL query into **msg.topic** , in order to insert all these rows do I need to build these into a single query using a loop?

I'm fairly sure there is a better way, or am I missing something here?

The JSON data:

```auto
{"results":[
    {"id":1,"subject":"","status":"new","created_at":"2019-08-20T21:42:38Z","description":"Foo Foo"},
    {"id":2,"subject":"","status":"new","created_at":"2019-08-21T02:27:55Z","description":"Foo Bar"},
    {"id":3,"subject":"","status":"new","created_at":"2019-08-21T02:27:55Z","description":"Bar Foo"}],
"count": 3
}

```

---

<div class="post-metadata">

**Author:** ![bakman2](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/bakman2/32/6207_2.png) [@bakman2](https://discourse.nodered.org/u/bakman2)\
**Post date:** [21 August 2019 11:28 UTC](https://discourse.nodered.org/t/json-array-to-mysql/14597/2 "2019-08-21T11:28:14Z")

</div>

Perhaps you can fiddle around with the batch/split node, but a simple loop works, why is this not the "better" way ?

```auto
[{"id":"bc8e4595.957348","type":"inject","z":"16dae769.f07d41","name":"","topic":"","payload":"{\"results\":[{\"id\":1,\"subject\":\"\",\"status\":\"new\",\"created_at\":\"2019-08-20T21:42:38Z\",\"description\":\"Foo Foo\"}, {\"id\":2,\"subject\":\"\",\"status\":\"new\",\"created_at\":\"2019-08-21T02:27:55Z\",\"description\":\"Foo Bar\"}, {\"id\":3,\"subject\":\"\",\"status\":\"new\",\"created_at\":\"2019-08-21T02:27:55Z\",\"description\":\"Bar Foo\"}], \"count\": 3 }","payloadType":"json","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":218,"y":532,"wires":[["95859c31.43c498","b4999cd4.83d0d8"]]},{"id":"4291b326.2a5d2c","type":"debug","z":"16dae769.f07d41","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":638,"y":532,"wires":[]},{"id":"95859c31.43c498","type":"function","z":"16dae769.f07d41","name":"","func":"m = msg.payload.results\n\nfor(x=0;x<m.length;x++){\n t = \"insert into table set element='\"+m[x].status+\"', description='\"+m[x].description+\"'\"\n node.send({topic:t})\n}\n","outputs":1,"noerr":0,"x":420,"y":532,"wires":[["4291b326.2a5d2c"]]},{"id":"b4999cd4.83d0d8","type":"debug","z":"16dae769.f07d41","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":330,"y":616,"wires":[]}]

```

---

<div class="post-metadata">

**Author:** ![hobojoe720](https://avatars.discourse-cdn.com/v4/letter/h/76d3ee/32.png) [@hobojoe720](https://discourse.nodered.org/u/hobojoe720)\
**Post date:** [21 August 2019 11:38 UTC](https://discourse.nodered.org/t/json-array-to-mysql/14597/3 "2019-08-21T11:38:29Z")

</div>

I didnt realise you could send the topic multiple times. what I had in my head was a loop that was appending to a string to build a single insert statement

---

<div class="post-metadata">

**Author:** ![bakman2](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/bakman2/32/6207_2.png) [@bakman2](https://discourse.nodered.org/u/bakman2)\
**Post date:** [21 August 2019 11:41 UTC](https://discourse.nodered.org/t/json-array-to-mysql/14597/4 "2019-08-21T11:41:42Z")

</div>

I am not sure if the mysql node supports multiple statements, but if it does, that could also be a solution.

---

<div class="post-metadata">

**Author:** ![hobojoe720](https://avatars.discourse-cdn.com/v4/letter/h/76d3ee/32.png) [@hobojoe720](https://discourse.nodered.org/u/hobojoe720)\
**Post date:** [21 August 2019 11:48 UTC](https://discourse.nodered.org/t/json-array-to-mysql/14597/5 "2019-08-21T11:48:01Z")

</div>

> [@bakman2](#):
>
> the mysql node supports multiple stat

Cant see why it wouldnt, its basically a single statement: "insert into table (col1, col2) values (val1, val2),(val3,val4)"

Your method is much cleaner however. And actually solves another issue where I need to clear the table before inserting these rows, so I can do an early node.send with delete from table.

---

<div class="post-metadata">

**Author:** ![bakman2](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/bakman2/32/6207_2.png) [@bakman2](https://discourse.nodered.org/u/bakman2)\
**Post date:** [21 August 2019 11:51 UTC](https://discourse.nodered.org/t/json-array-to-mysql/14597/6 "2019-08-21T11:51:24Z")

</div>

`insert into table (col1, col2) values (val1, val2),(val3,val4)`  
Is a single insert, i thought you wanted to create 3 rows.  
It could be more simple in that case.

---

<div class="post-metadata">

**Author:** ![hobojoe720](https://avatars.discourse-cdn.com/v4/letter/h/76d3ee/32.png) [@hobojoe720](https://discourse.nodered.org/u/hobojoe720)\
**Post date:** [21 August 2019 11:57 UTC](https://discourse.nodered.org/t/json-array-to-mysql/14597/7 "2019-08-21T11:57:55Z")

</div>

It still inserts each set of values as a new row.