What is the purpose of the JSON type column in MYSQL?

Question

What is the purpose of the JSON type column in MYSQL?

Navigation

#1 by (12 votes)

11

I noticed that Mysql has released a new feature, which is the column can be type JSON (which can even be saved in binary, according to what I was reading).

Documentation :

As of MySQL 5.7.8, MySQL supports a JSON data type that enables efficient access to data in JSON (JavaScript Object Notation) documents.

That translating is:

From MySQL 5.7.8 , MySQL natively supports the JSON data type that allows efficient access to data in% (JavaScript Object Notation)%.

With this implementation, what are the benefits gained? Is there any difference in performance or ease of recovery of these values?
Simply using a field of type JSON to save my TEXT was not enough?

mysql json

asked by anonymous 16.09.2016 / 21:30

1 answer

"java.lang.SecurityException: Permission Denial" on Android 6 Marshmallow (API 23) What is the difference between the tag and the s?

score 12 · Accepted Answer

There are many advantages to the JSON field compared to TEXT :

Validation - The data is automatically validated. If the JSON is invalid, the record will not be entered and the operation will produce an error
Efficient Access - The storage format is optimized. JSON documents saved in columns of type JSON are actually converted into an internal format that allows quick reading of document elements. When the server needs to read a JSON value stored in this binary format, the value does not need to be converted from a text representation.
Performance - You can improve query performance by creating indexes within the JSON columns. This is possible through the secondary indexes in virtual columns .
Convenience - It's much easier to retrieve values in the queries through the JSON_EXTRACT or with the new coluna->caminho syntax:

SELECT nome, conta->"$.saldo" FROM clientes WHERE conta->"$.atrasada" = true;

So now it's much easier to manipulate the data and store those complex values in a column. It can be said that MySQL supports a non-relational (NoSQL) data structure within a relational structure. Incredible!

However, as the article says MySQL 5.7 brings sexy back with JSON , note that:

NoSQL specialized databases (document-oriented banks, key-value banks, and graph-based banks) remain better options for your specific use cases, but adding this type of data may allow you to reduce complexity of its technology stack.