I am trying to insert data from an API post request to fill out the rows in a MySQL table. I want the JSON array which has multiple objects in to occupy the rows in the table. I can't seem to figure it out using express JS. I am hoping someone can assist me with this problem.
Expected Result:
Below is my JS code snippet:
app.post('/dump', (req,res) => {
let info = req.body;
var sql = "INSERT INTO `testStuff` (`j_object`) VALUES(?)";
mysqlConnection.query(sql,[req.body],(err,rows,fields) => {
if (!err)
res.send('insert success');
else
console.log(err)
})
});
Example Post Body:
{
"test": [
{
"id": 20,
"name": "John Doe",
"location": "NY"
},
{
"id": 21,
"name": "Betty Doe",
"location": "FL"
}
]
}
MYSQL Table:
CREATE TABLE `testStuff` (
`j_object` json NOT NULL
)
INSERT INTO testStuff (j_object)
SELECT value
FROM JSON_TABLE(@post_body, '$.test[*]' COLUMNS (value JSON PATH '$')) jsontable
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=b8394f7e3e80942fcae063441bed7af1
In var sql = ... use ? instead of @post_body.