How to get JSON type as JSON instead of String from Nodejs Mysql

Question

I have table say TEST(id INT, attribute JSON) in MySQL 5.7

When I try to query the table in Nodejs using mysql package as follows

con.query("select * from TEST where id=?", [req.params.id], function (err, results) {
    if (err) throw err;
    console.log(results);
  });

I get the following output

[
   {
        "id": 2,
        "package": "{\"tag\": \"tag1\", \"item\": \"item1\"}"
    }
]

Is there a way to get the package item in the above result as JSON object instead of a string without iterating the array and do JSON.parse to convert string to JSON?

Expected Output

[
       {
            "id": 2,
            "package": {"tag": "tag1", 
                        "item": "item1"}
        }
    ]

Answer 1

This is a slight variation on Tanner's answer but, if you're using the node mysql library and you've defined fields in the database using MySQL's JSON data type, you can use "type casting" when initially setting up the connection to convert any JSON-typed value returned by any query to a JSON object:

let connection = mysql.createConnection(
  {typeCast: function (field, next) {
    if (field.type === "JSON") {
      return JSON.parse(field.string());
    } else {
      return next();
    }
  }}
);

Answer 2

Is there a way to get the package item in the above result as JSON object instead of a string without iterating the array and do JSON.parse to convert string to JSON?

MySQL 5.7 supports JSON data type, so you can change package type to JSON and you won't have to iterate and perform JSON.parse on each row, if your client has support for this data type.

Have in mind that mysql package does not support it, but mysql2 does.

CREATE TABLE `your-table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `package` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

Now package will be an array/object:

con.query("select * from TEST where id=?", [req.params.id], function (err, results) {
    if (err) throw err;
    console.log(results[0].package.tag); // tag1 using mysql2
    console.log(results[0].package.item); // item1 using mysql2
});

In case you're running a MySQL version lower than 5.7 or you don't want to use the JSON type, you will need to iterate and parse it yourself.

You can use this one liner:

results = results.map(row => (row.package = JSON.parse(row.package), row));

If you want to know if you should or shouldn't store JSON in a relational database there's a nice discussion in this question:

Storing JSON in database vs. having a new column for each key

Answer 3

You can pass a typeCast function to your mysql connection/pool. In my case, I want to convert all longtext fields to JSON if possible. In the case it fails, you will still get the original value.

let auth = {database:'db', password:'pw', user:'u', host:'host.com' };
auth.typeCast = function(field, next) {
    if (field.type == 'BLOB' && field.length == 4294967295) {
        let value = field.string();
        try {
            return JSON.parse(value);
        } catch (e) {
            return value;
        }
    }
    return next();
};
let connection = mysql.createConnection(auth);