Insert nested JSON to MySQL using Node

Question

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:

Field: "userID", INT
Field: "id", INT
Field: "version", INT

Plotisateur · Accepted Answer · 2017-03-30 04:18:11Z

1

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];
});

edited Mar 30, 2017 at 4:18

answered Mar 30, 2017 at 1:58

Plotisateur

50610 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Jim Dover Over a year ago

Thank you! I am retrieving the JSON via an API call. So to get rid of the nested objects, I would need to use firstly json.parse to convert my JSON?

Plotisateur Over a year ago

You're welcome ;) Actually if your API already send JSON, JSON.parse is useless. What you need to do is either changing the id of the employeeDetails object to userId so there is no conflict with the first one and then plain it or iterate through the array and objects to go with Bulk INSERT

Jim Dover Over a year ago

Thanks! I guess what I'm confused with is getting the JSON object from my original nested type to - var result2 = [[89304, 102524, 9], [89305, 102522, 99]];

Plotisateur Over a year ago

I added a way to convert your original array ;)

Jim Dover Over a year ago

Life saver! Thank you do much!

Collectives™ on Stack Overflow

Insert nested JSON to MySQL using Node

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related