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
– RiggsFolly, Commented Jan 29, 2021 at 17:31

Barmar · Accepted Answer · 2021-01-29 17:37:41Z

2

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

answered Jan 29, 2021 at 17:37

Barmar

789k57 gold badges554 silver badges669 bronze badges

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related