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-17 22:21:22Z

12

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

edited Apr 17, 2018 at 22:21

answered Apr 17, 2018 at 3:35

Marcos Casagrande

40.7k11 gold badges93 silver badges107 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Deepan Over a year ago

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

Marcos Casagrande Over a year ago

@Deepan check my updated answer, you have to use mysql2 package! And you didn't mention you were using JSON data type.

Deepan Over a year ago

Thanks Marcus. mysql2 package did the work. I replaced mysql with mysql2 node package.

James BonTempo · Accepted Answer · 2021-12-01 22:27:17Z

5

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

answered Dec 1, 2021 at 22:27

James BonTempo

531 silver badge4 bronze badges

Comments

tanner burton · Accepted Answer · 2019-11-19 00:02:26Z

1

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

answered Nov 19, 2019 at 0:02

tanner burton

1,1691 gold badge15 silver badges17 bronze badges

Comments

Toni · Accepted Answer · 2021-10-28 22:24:52Z

0

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

edited Oct 28, 2021 at 22:24

Toni

1,6635 gold badges21 silver badges27 bronze badges

answered Oct 28, 2021 at 1:01

Huanyu

313 bronze badges

Comments

Dan Oswalt · Accepted Answer · 2018-04-17 03:25:39Z

-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, 2018 at 3:25

answered Apr 17, 2018 at 2:57

Dan Oswalt

2,1892 gold badges15 silver badges24 bronze badges

3 Comments

Marcos Casagrande Over a year ago

results is already an array, that won't work. He has to iterate and JSON.parse each package.

Marcos Casagrande Over a year ago

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

Dan Oswalt Over a year ago

Got ahead of myself, @MarcosCasagrande, ty

Collectives™ on Stack Overflow

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

5 Answers 5

3 Comments

Comments

Comments

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

Comments

Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related