CODEWITHSUNDEEP
javascriptmysqlsqlnode.jsjson
ismsm
ismsm

Reputation: 173

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

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?

Upvotes: 1

Views: 1634

Answers (1)

Barmar
Barmar

Reputation: 782498

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

Upvotes: 2

Related Questions