JSON array to MySQL

General
1

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:

{"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
}
2

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

[{"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":[]}]
3

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

4

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

5

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.

6

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.

7

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