Skip to content

MariaDB and Native JSON support ?

A question raised by my previous post is : What about MariaDB and native JSON support ? In my previous post I mentioned the possibility to use the MariaDB CONNECT storage Engine to store and access JSON content in normal text field. Of course having a native JSON datatype brings more value. It introduces JSON validation, a more efficient access to attribute through an optimized storage format.

What about MariaDB and native JSON support ?
Native JSON support, indexing pure virtual column and optimizer improvements to smartly decide to use this index will come with MariaDB 10.2.
These JSON features are part of MariaDB 10.2 roadmap. Here is the MariaDB 10.2 roadmap.
For a more detailed features list for MariaDB 10.2 : Here is a full list of MariaDB 10.2 community voted features
The MariaDB engineering team has taken it time to evaluate various aspect of native JSON implementation. Two different implementations exist. Beside Oracle's MySQL implementation Facebook has developed its own implementation called DocStore that is part of the Facebook MySQL branch. Both have been evaluated by MariaDB engineering. Another area that must be addressed is the current evolution of the standard regarding SQL/JSON

Oracle MySQL 5.7 already supports a Native JSON data type. This JSON datatype comes with an extensive set of functions to create/modify/search this JSON content. Oracle MySQL 5.7 has also introduced virtual columns (call generated columns). MySQL 5.7 Generated columns can be materialized or not. MariaDB already had virtual columns but Oracle MySQL 5.7 is currently ahead of MariaDB 10.1 as it is possible to add an index on a pure virtual column that is not persistant. MySQL 5.7 optimizer has also been improved to detect the use in the where clause of an expression corresponding to the virtual column. These virtual columns with indexing capabilities allows optimized filtering of native JSON datatype through a WHERE clause.

Neither MariaDB 10.2 nor Oracle MySQL 5.7 implement inplace update of JSON content. This is an area where a fully JSON based datastore like MongoDB performs better. This advantage is balanced by extra space consumption to introduce padding. This extra padding space is required to avoid having to move data around and to avoid to have to update many indexes for a single field update. This area definitely needs to be benchmark and depends on JSON content size and access pattern.

However for certain use cases Native JSON has some drawbacks compare to to storing JSON into a text field. The insert rate is much lower as JSON needs to be parsed and converted to the internal storage format. If the client application is completely handling the JSON content we are wasting server resources.

So for MariaDB fans and users be patient native JSON is coming with MariaDB 10.2. And of course feel free to comment. Our engineering and development is not done behind closed doors and you can be part of it. 😉

Leave a Reply

Your email address will not be published. Required fields are marked *