Node-Express MySQL API output JSON array as string

Question

Try setting up an Express API server to get some data for the portfolio website. I have set up a MySQL table with a JSON data type for my "Image" column. "images" should have multiple image links for the gallery. However, the server outputs the image array as a string instead of an array of strings.

Javascript code on the API server

app.get("/getWorks", (req, res) => {
  let sql = "select * from works";
  db.query(sql, (err, result) => {
    if (err) throw err;
    console.log(result);
    res.send(result);
  });
});

result

[
  {
    "workID": 1,
    "title": "example",
    "images": "[\"https://SERVER_IP/images/example.png\", \"https://SERVER_IP/images/example.png\"]"
  }
]

Solution

I found a workaround to get the desired output, adding the following:

result = result.map((row) => ((row.images = JSON.parse(row.images)), row));

[
  {
    "workID": 1,
    "title": "example",
    "images": ["https://SERVER_IP/images/example.png", "https://SERVER_IP/images/example.png"]
  }
]

Why doesn't the query output the data in the JSON array in the first place even though I specify that specific column as JSON data type in the table?

Answer 1

I solved this problem. I'm using the wrong MySQL node package. Requires MySQL2 for json formatting.

npm install mysql2

const mysql = require("mysql2");