JSON Labs Release: Native JSON Data Type and Binary Format

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:

  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:

    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!

16 thoughts on “JSON Labs Release: Native JSON Data Type and Binary Format

  1. I’d love to sign up for notifications for when this feature makes it into MySQL Community Server. Are there any specific timeline for moving this feature into the core?

  2. Hello,
    I am not an expert in mysql, but I am interested in json data in MySql.
    I have installed ‘mysql-installer-community-’
    Then, using MySql Work bench, impossible to create interactively JSON column in a table (not in colum type combo). But I succeed with SQL query or php code.
    I try your tutorial, but fails for selecting data. For exemple, request :
    mysql> select jsn_extract(data, ‘$.name’) from employees;
    returns “ERROR 1305 (42000): FUNCTION jsn_extract does not exist”
    What is the problem ?

    Best regards.

    1. Hi, we are still working on the JSON datatype support for MySQL Workbench. That’s the reason the Table Editor does not support it yet. An updated version of MySQL Workbench 6.3 that includes this functionality will be released together with the MySQL 5.7 GA release. For now, please keep using the SQL panel to execute the JSON commands. About the JSON_EXTRACT function not working – please check the manual here https://dev.mysql.com/doc/refman/5.7/en/json.html You will notice that the function name has been changed to JSON_EXTRACT(), you are using jsn_extract() missing the “o”. Hope that helps! Best, Mike

      1. If that’s the case, then the included example provides inaccurate syntax:

        | jsn_extract(data, ‘$.name’) |
        | “Jane” |
        | “Joe” |
        2 rows in set (0,00 sec)

        Note: It reads jsn_extract, not json_extract 🙂

Leave a Reply