The latest from the MySQL community

The MySQL 5.7.7 JSON Lab release introduces a native JSON datatype. In part 1 of this blog post series, Rick Hillegas introduced the new functions for creating and manipulating JSON documents using the new native JSON data type. In this blog post we will be using some of the same sample tables and JSON documents as in part 1, so it will be helpful to read that blog post now, if you haven’t already.

Here we look closer at the functions provided to search through and peer into JSON documents to find values inside of them, and we’ll also cover a few utility functions as we go along. The functions described here are also summarized at the end of this blog for ease of reference.

Please note that the exact set of functions and their semantics may evolve …

The MySQL 5.7.7 JSON Lab release introduces a native JSON datatype. See Knut Anders Hatlen’s blog post for more details on this new datatype. In this release we also introduced a number of functions for creating and querying JSON documents. In this post we’ll explore the following new functions related to manipulating JSON documents:

• jsn_array()
• jsn_object()
• jsn_insert()
• jsn_remove()
• jsn_set()
• jsn_replace()
• jsn_append()
• jsn_merge()
• jsn_extract()

Dag Wanvik’s follow up blog …

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:

mysql> CREATE TABLE employees (data JSON);
Query OK, 0 rows affected (0,01 sec)

mysql> INSERT INTO employees VALUES ('{"id": 1, "name": "Jane"}');
Query OK, 1 row affected (0,00 sec)

mysql> INSERT INTO employees VALUES ('{"id": 2, "name": "Joe"}');
Query OK, 1 row affected (0,00 sec)

mysql> select * from employees;
+---------------------------+
| data                      |
+---------------------------+
| {"id": 1, "name": "Jane"} |
| {"id": 2, "name": "Joe"}  |
+---------------------------+
2 rows in set (0,00 sec)

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 …

In MySQL 5.7.6, we added a new feature called Generated Columns. In the initial work all Generated Columns were materialized, even virtual ones. This not only resulted in unnecessary disk space being used and disk I/O being done, but it also meant that any table alteration required that the full table be rebuilt. In the new MySQL 5.7.7 JSON Lab release, we have resolved all of these issues by implementing new features that not only allow users to create non-materialized virtual …

Can we output content of a mariaDB table into JSON using the CONNECT Storage Engine ? MariaDB and MySQL are not currently JSON friendly databases. The usage of JSON in MySQL is almost inexistent with the excetion of the explain JSON output and of MariaDB dynamic column dump. There also exist a few udf (in [...]

JavaScript has emerged in the past couple years as the de facto expression of next generation web technologies, and a critical component of HTML5 technologies – along with CSS and JQuery. JavaScript is really ubiquitous – it really is showing up everywhere! There are many other reasons why JavaScript and JS-based tools are becoming the wave of the future. JavaScript is the #1 most-used language on GitHub, and this trend is only going to increase. As one article put it, “JavaScript is the number one …

What is JSON

JSON is an text based, human readable format for transmitting data between systems, for serializing objects and for storing document store data for documents that have different attributes/schema for each document. Popular document store databases use JSON (and the related BSON) for storing and transmitting data.

Problems with JSON in MySQL

It is difficult to inter-operate between MySQL and MongoDB (or other document databases) because JSON has traditionally been very difficult to work with. Up until recently, JSON is just a TEXT document. I said up until recently, so what has changed? The biggest thing is that there are new JSON UDF by Sveta Smirnova, which are part of the MySQL 5.7 Labs releases. Currently the JSON UDF are up to version 0.0.4. While these new UDF are a welcome edition to the MySQL database, they don’t solve the really tough …

What is JSON
JSON is an text based, human readable format for transmitting data between systems, for serializing objects and for storing document store data for documents that have different attributes/schema for each document. Popular document store databases use JSON (and the related BSON) for storing and transmitting data.

Problems with JSON in MySQL
It is difficult to inter-operate between MySQL and MongoDB (or other document databases) because JSON has traditionally been very difficult to work with. Up until recently, JSON is just a TEXT document. I said up until recently, so what has changed? The biggest thing is that there are new JSON UDF by Sveta Smirnova, which are part of the MySQL 5.7 Labs releases. Currently the JSON UDF are up to version 0.0.4. While these new UDF are a welcome edition to the MySQL database, they don't solve the really tough …

New version of JSON UDF functions has been just released. This version introduces two new features.

1. Escape characters in results of JSON_SEARCH function. In previous version if you had document @doc = '{"foo:bar": "baz"}' or '{"foo": {"bar": "baz"}}' return value of JSON_SEARCH(@doc, '"baz"'); was 'foo:bar::' for both. There was no way to distinguish two search paths. Now for the first document JSON_SEARCH returns 'foo\:bar::'
2. Second feature is much bigger. Now JSON functions accessing elements by a key (JSON_CONTAINS_KEY, JSON_EXTRACT, JSON_REPLACE, JSON_REMOVE, JSON_APPEND, JSON_SET, JSON_COUNT) can use alternate keypath syntax: $.key1.key2[arr_index] where $ is root element, .key is key name, [arr_index] is array index. JSON_SEARCH can also return path in this format with escaped $, . and [] symbols.

MariaDB 10.1.3 was recently released, and is available for download here:

https://downloads.mariadb.org/mariadb/10.1.3/

This is the 1st beta, and 4th overall, release of MariaDB 10.1, so there are a lot of new changes, functionalities added, defaults changed, and many bugs fixed (I counted 420 – 117 in 10.1.2 & 637 in 10.1.1, fwiw).

Since it’s beta, I’ll only cover the major changes and additions, and omit covering general bug fixes (feel free to browse them all here).

To me, these are the highlights of the new features:

• Table and Tablespace Encryption.
• …