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"}
        }
    ]

Marcos Casagrande · Accepted Answer · 2018-04-17T22:21:22.213

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

Thanks Marcos for the reply. I am using the JSON as the data type as mentioned in my question. The results from mysql query node module are automatically converted to STRING by default i.e I can't access directly as JSON object. I need to do JSON.parse(results[0].package)... — Deepan, Apr 17 '18 at 22:16
@Deepan check my updated answer, you have to use `mysql2` package! And you didn't mention you were using JSON data type. — Marcos Casagrande, Apr 17 '18 at 22:22
Thanks Marcus. mysql2 package did the work. I replaced mysql with mysql2 node package. — Deepan, Apr 17 '18 at 22:25

score 5 · Answer 2 · answered Dec 01 '21 at 22:27

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

score 1 · Answer 3 · answered Nov 19 '19 at 00:02

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

score 0 · Answer 4 · edited Oct 28 '21 at 22:24

0

You can use package mysql2 instead of mysql. It will parse the result by default.

edited Oct 28 '21 at 22:24

Toni

1,555
4
15
23

answered Oct 28 '21 at 01:01

Huanyu

31
3

Dan Oswalt · Answer 5 · 2018-04-17T03:25:39.677

-2

I think all you need to do is JSON.parse(results)

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

});

EDIT! commenter noted that you need to iterate through results, my answer was hasty and wouldn't work:

con.query("select * from TEST where id=?", [req.params.id], function (err, results) {
if (err) throw err;
results = results.map(row => (row.package = JSON.parse(row.package), row));

});

edited Apr 17 '18 at 03:25

answered Apr 17 '18 at 02:57

Dan Oswalt

2,201
2
14
24

1

`results` is already an array, that won't work. He has to iterate and `JSON.parse` each package. – Marcos Casagrande Apr 17 '18 at 02:59
1

still won't work :P, you need to use `JSON.parse` on each row `package`. so `results = results.map(row => (row.package = JSON.parse(row.package), row))` – Marcos Casagrande Apr 17 '18 at 03:23
1

Got ahead of myself, @MarcosCasagrande, ty – Dan Oswalt Apr 17 '18 at 03:27

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

5 Answers5