Using JSON

As you have seen in the previous chapter, to store data in MySQL, you have to define the database and table structure (schema), which is a major limitation. To cope with that, from MySQL 5.7, MySQL supports the JavaScript Object Notation (JSON) datatype. Earlier there was no separate datatype and it was stored as a string. The new JSON datatype provides automatic validation of JSON documents and optimized storage format.

JSON documents are stored in binary format, which enables the following:

• Quick-read access to document elements
• No need for the value to be parsed from a text representation when the server reads the JSON again
• Looking up subobjects or nested values directly by key or array index without reading all values before or after them in the document

How to do it...

Suppose you want to store more details about your employees; you can save them using JSON:

CREATETABLEemp_details(
emp_no intprimary key,
detailsjson
);

Insert JSON

INSERTINTOemp_details(emp_no, details)
VALUES('1...