How to insert values from JSON data with multiple objects into rows in MySQL?

Question

I can store a value in a JSON data with one object into MySQL data base, like this:

var data = '{"sensorvalue":"96"}'

const jsdata = JSON.parse(data);
connection.query("INSERT INTO `nodes`(`sensorvalue`) VALUES ('"+jsdata.sensorvalue+"')", (err, res) => {
    console.log("counter record inserted");  
});

The output:

id	sensorvalue
1	96

However, I want to store values in a JSON data with two or more objects, like this:

var data = '[{"sensorvalue":"96"},{"sensorvalue":"98"}]'

But, it stores it as a undefined in row in table.

The expected output:

id	sensorvalue
1	96
2	98

I have seen this questionbefore but I don't know how this could help me because in that question is about array of arrays not a JSON data with multiple objects. How could I solve this, please?

In `[{"sensorvalue":"96"},{"sensorvalue":"98"}]` Do you not see an array of objects? — RiggsFolly, Jan 29 '21 at 17:31

score 2 · Accepted Answer · answered Jan 29 '21 at 17:37

It's an array, you need to loop over it.

You also shouldn't substitute values directly into the SQL, use parametrized queries.

const jsArray = JSON.parse(data);
jsArray.forEach(jsdata =>
  connection.query("INSERT INTO `nodes`(`sensorvalue`) VALUES (?)", [jsdata.sensorvalue], (err, res) => {
    console.log("counter record inserted");
  })
);

The node.js MySQL connector also allows you to use a 2-dimensional array to insert values in multiple rows at once. See How do I do a bulk insert in mySQL using node.js

const sensorValues = JSON.parse(data).map(el => [el.sensorvalue]);
connection.query("INSERT INTO `nodes`(`sensorvalue`) VALUES ?", sensorValues, (err, res) => {
  console.log(`${sensorValues.length} counter records inserted`);
});

How to insert values from JSON data with multiple objects into rows in MySQL?

1 Answers1