In the MySQL 5.7.7 JSON labs release, we have introduced a new data type for storing JSON data in MySQL tables. Now you can do this:

Sure, you could always store JSON data in a TEXT or VARCHAR column, but having a native data type for JSON provides some major benefits over that approach:

1. Document Validation

   Only valid JSON documents can be stored in a JSON column, so you get automatic validation of your data. If you try to store an invalid JSON document in a JSON column, you will get an error:

   JSON Validation Example

   MySQL

   mysql> INSERT INTO employees VALUES ('some random text'); ERROR 3130 (22032): Invalid JSON text: "Expect a value here." at position 0 in value (or column) 'some random text'.

   1 2	mysql> INSERT INTO employees VALUES ('some random text'); ERROR 3130 (22032): Invalid JSON text: "Expect a value here." at position 0 in value (or column) 'some random text'.

2. Efficient Access
   More importantly, when you store a JSON document in a JSON column, it is not stored as a plain text value. Instead, it is stored in an optimized binary format that allows for quicker access to object members and array elements.

   Let’s say you want to extract the names of all employees in the table above. You can do that with the following query:

   JSON Search/Extraction Example

   MySQL

   mysql> select jsn_extract(data, '$.name') from employees; +-----------------------------+ | jsn_extract(data, '$.name') | +-----------------------------+ | "Jane" | | "Joe" | +-----------------------------+ 2 rows in set (0,00 sec)

   1 2 3 4 5 6 7 8

   mysql> select jsn_extract(data, '$.name') from employees; +-----------------------------+ | jsn_extract(data, '$.name') | +-----------------------------+ | "Jane"                      | | "Joe"                       | +-----------------------------+ 2 rows in set (0,00 sec)

   If the JSON data had been stored in a text column as plain JSON text, you would have had to run the text through a JSON parser, which would have had to scan the JSON document from the beginning and all the way until it found the ‘name’ key.

   The binary format of a JSON column, on the other hand, contains a preamble with a lookup table. The lookup table has pointers to every key/value pair in the JSON document, sorted on the key. This allows the JSN_EXTRACT function to perform a binary search for the ‘name’ key in the table and read the corresponding value directly, without having to parse the ‘id’ key/value pair that precedes it within the JSON document.

   And this is the primary purpose of the new JSON data type—providing an efficient way of accessing the data within JSON documents. The example above shows how the JSN_EXTRACT function takes advantage of this. The JSN_EXTRACT function is a new function in the MySQL 5.7.7 JSON labs release. In fact, the labs release introduces a whole series of functions to access and manipulate JSON documents, and all of them rely on the binary JSON format in order to provide better performance than a plain text-based storage format could provide.

For more information about what you can do with the new JSON data type, you can read about all the new JSON functions in these additional blog posts: JSON Functions, Part 1, and JSON Functions, Part 2.

Lastly, you can also vastly improve the query performance by creating indexes on values within the JSON columns. This can be achieved with “functional indexes” on virtual columns. That is explained in greater detail in this blog post.

And if you want to dig further into the details of how JSON values are stored, the exact storage format is described in WL#8132.

Please let us know what you think of these new JSON features! We’d love to hear your feedback on what else you’d like to see related to our wider JSON support. If you encounter any problems with these new features, please let us know here in the comments, open a bug report at bugs.mysql.com, or open a support ticket.

Thank you for using MySQL!