CODEWITHSUNDEEP
javascriptmysqljsonnode.js
Jim Dover
Jim Dover

Reputation: 623

Insert nested JSON to MySQL using Node

I am using Node to INSERT JSON to MySQL, using Node.js

This works fine, until I have nested values.

Example of my JSON is here:

var result2 = [{
"id": 89304,
"employeeDetails": {
    "id": 102524,
    "version": 9
}
}, {
"id": 89305,
"employeeDetails": {
    "id": 102522,
    "version": 99
}
}];

Note: I am using the above for testing, as further on I want to call API requests in JSON and return them.

This is how I am sending it to MySQL using Node.js:

var query = connection.query('INSERT INTO employees SET ?', result2,
function(error, results, fields) {
    if (error) throw error;
});

Without nesting, this is working fine, however the nested employeeDetails gives me the following error:

Error: ER_BAD_FIELD_ERROR: Unknown column 'employeeDetails' in 'field list'

My current table format is:

Upvotes: 0

Views: 1752

Answers (1)

Plotisateur
Plotisateur

Reputation: 506

As stated in the doc https://www.npmjs.com/package/mysql#escaping-query-values :

Objects are turned into key = 'val' pairs for each enumerable property on the object. If the property's value is a function, it is skipped; if the property's value is an object, toString() is called on it and the returned value is used.

So you can't use nested objects because it's considered as a field.

You would instead do a loop through your array of objects :

var result2 = [{
  "id": 89304,
  "userId": 102524,
  "version": 9
}, {
  "id": 89305,
  "userId": 102522,
  "version": 99
}
}];
for(var i in result2) {
  var query = connection.query('INSERT INTO employees SET ?', result2[i],
  function(error, results, fields) {
    if (error) throw error;
  });
}

Or more simply, if your goal is to accomplish a bulk INSERT, using nested arrays :

var result2 = [[89304, 102524, 9], [89305, 102522, 99]];
var query = connection.query('INSERT INTO employees(id, userId, version) VALUES ?', [result2],
function(error, results, fields) {
  if (error) throw error;
});

There a method to convert from your original nested objects to Bulk INSERT : 

var result2 = result2.map(function(el){
    return [el.id, el.employeeDetails.id, el.employeeDetails.version];
});

Upvotes: 1

Related Questions