insert JSON into mysql json column

Question

I am getting this error when trying to insert a record

code: 'ER_PARSE_ERROR',

errno: 1064,

sqlMessage: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[{"fieldname":"files","originalname":"node-v20.11.1-x64.msi","encoding":"7bit","' at line 1,

sqlState: '42000',

index: 0,

sql: INSERT INTO product (firstName,lastName,files) VALUES ('test1', 'SLIM', JSON_OBJECT([{"fieldname":"files","originalname":"node-v20.11.1-x64.msi","encoding":"7bit","mimetype":"application/octet-stream","destination":"uploads","filename":"node-v20.11.1-x64.msi","path":"uploads\\\\node-v20.11.1-x64.msi","size":26636288},{"fieldname":"files","originalname":"VSCodeUserSetup-x64-1.87.0.exe","encoding":"7bit","mimetype":"application/x-msdownload","destination":"uploads","filename":"VSCodeUserSetup-x64-1.87.0.exe","path":"uploads\\\\VSCodeUserSetup-x64-1.87.0.exe","size":98146456}]));

Backend

app.post('/', upload.array('files'), (req,res) => {
    let firstName = req.body.firstName;
    let lastName = req.body.lastName;
    let filename = JSON.stringify(req.files);
    console.log("filename:" + filename);
    let postQuery = "INSERT INTO product (firstName,lastName,files) VALUES ('" + firstName + "', '" + lastName + "',  JSON_OBJECT(" + filename + "));";
    console.log(postQuery);
    connection.query(postQuery, (err,result,fields) => {
        if (err) { throw err };
        
        res.send('Response has been recorded...');
    })
})

mysql table mysql table

How can I make it insert the JSON on the files column

Answer 1

You can insert json directly without using JSON_OBJECT function. JSON_OBJECT has different syntax and purposed to create dynamically create json.