JSON type - Encoding issue

[deniapps]

We have JSON Type data field with some Latin characters, for example: "açaí". We used to use node-mysql, which queries the JSON filed as String, and we use JSON.parse to get the JSON object, which returns the correct character set, i.e. "açaí".

We switched to node-mysql2, and used typeCast to skip auto JSON parsing, like this:

typeCast: (field, next) => {
   if (field.type === 'JSON') {
      return field.string();
   }
   return next();
}

Everything works fine, but it does not handle encoding correctly, in the case, "açaí" is displayed as "açaí".

I am wondering how field.string() works. Does it use "UTF-8"? Can we return the JSON field using String as node-mysql does?

[sidorares]

I believe it's using connection encoding. What's used in your connection config? Can you try with UTF8MB4_UNICODE_CI ?

[sidorares]

actually it's using whatever encoding server reported for field metadata -

node-mysql2/lib/parsers/text_parser.js

Line 149 in 442d304

const encodingExpr = `CharsetToEncoding[fields[${i}].characterSet]`;

so in theory can be multiple charsets per table / resultset

Can you show your table schema?

[deniapps]

I believe it's using connection encoding. What's used in your connection config? Can you try with UTF8MB4_UNICODE_CI ?

Thank you for the quick reply. I just figured this out.
The character set of JSON type in MySQL is 'binary'. Regarding the doc https://github.com/mysqljs/mysql#type-casting:

Note text in the binary character set is returned as Buffer, rather than a string.

So I have to do this:

typeCast: (field, next) => {
   if (field.type === 'JSON') {
      try {
         const jsonString = field.buffer().toString();
         return jsonString;
       } catch (error) {
          return null;
       }
   }
   return next();
}

Note that I have to use trycatch here for my JSON field could be NULL

Hope this helps for someone else.

[deniapps]

Actually, I still have a question. If field.string() returns Buffer for the binary character set, then why it works fine except for the encoding issue. (Somehow Buffer got auto-cast to String?)

[sidorares]

This depends on what encoding was actually used to store string, mysql encodings are a bit messy, see discussion here for example: #374 (comment)

in node buffer.toString() uses utf8 by default but if buffer content used some different encoding you might get unexpected result