corry corry - 1 year ago 104
JSON Question

Inserting multiple rows from JSON structure to MySQL

I'm stuck with inserting JSON data to MySQL db using NodeJS.
I got this error:

Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''[{\"id\":\"d3f3d\",\"code\":\"' at line 1

I'm getting JSON data from
using the
Node module and I'm trying to store the data in a MySQL db.

//mysql connection setup
var connection = mysql.createConnection({
host : "localhost",
port: "3306",
user : "root",
password : "root",
database : "db",
multipleStatements: true

request('url.json', function (error, response, body) {
if (!error && response.statusCode == 200) {

var sql = "INSERT INTO table (id, code, country_name, city) VALUES ?";

var data = JSON.parse(body);
var responseJson = JSON.stringify(;

var query = connection.query(sql, [responseJson], function(err, result) {
if(err) throw err;
console.log('data inserted');


The data is logged as ''
'[{\"id\":\"d3f3d\",\"code\":\"'... }]'
. I think this may be the source of the error.

JSON structure looks like this:


"country_name":"United States",

How do I resolve this?


Answer Source

The error stems from the query object's attempt to parse the values supplied. The function expects the values array to contain multiple arrays but received a string instead. The responseJson need not be converted into a JSON string.

You'll need to convert the array of objects into an array of arrays with only the values. For example, the query expects an array of this form:

    "United States",

This is a method to convert an array of objects into an array of values:

function ObjToArray(obj) {
  var arr = obj instanceof Array;

  return (arr ? obj : Object.keys(obj)).map(function(i) {
    var val = arr ? i : obj[i];
    if(typeof val === 'object')
      return ObjToArray(val);
      return val;

This is the code refactored:

var data = JSON.parse(body);
// Convert the array of objects into an array of arrays.
var responseJson = ObjToArray(;

// The query object expects an array of objects so you pass in 'responseJson' as is
var query = connection.query(sql, responseJson, function(err, result) {
    if(err) throw err;
    console.log('data inserted');

This issue explains the problem with bulk inserts for node-mysql.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download