Happy to see this MySQL JSON momentum !

Last week at Percona Live Facebook has presented for the first time Docstore which is a native JSON implementation in MySQL. Oracle has also presented their MySQL 5.7 lab release that includes the implementation of a native JSON type.

This is an important move as MySQL was behind other other RDMS regarding JSON (PostgreSQL already had a nice and complete implementation). JSON being widely adopted in various area (JS dev, data exchange and database format, …) this move was awaited.

The 2 implementations of Native JSON type are different but they both provide validation of input data and a binary format JSON storage.

If we look at the native JSON Oracle implementation the native JSON format is optimized for property retrieval. To achieve good performance the binary JSON storage format includes a preamble with a lookup table. This lookup table will speedup key/value pair access when executing functions like JSN_EXTRACT.

Speeding up JSN_EXTRACT is important for indexing JSON fields. For the Oracle implementation the JSON Indexes are indexes on a non-materialized Virtual Columns(functional indexes). These virtual column are a call to JSN_EXTRACT for the JSON property we want to index.

The Facebook native JSON implementation use a docpath syntax that is understood by the MySQL parser. It is possible to have a secondary composite index containing a JSON index part. The optimizer can use the secondary index for covering index, index only range scan … There is not yet a full optimizer support but this is a good start.

Contrary to Facebook, Oracle has not modified the parser and all is wrapped by JSON helper functions.

The main difference I sees between these 2 implementation is the fact that with oracle implementation the secondary index is bound to a virtual column that need to be in the DDL. With the Oracle implementation it is not possible to have a composite index mixing a regular column. So for the moment the Facebook implementation seems to be more flexible on this part.

What I like about the Oracle implementation is that the provided JSON function work both for native JSON format or for JSON text. That might explain why the udf function lab release has been remove from downloads.

So will it still make sense to store JSON as a text field ?

It depends on use case. Having no validation, no binary format conversion in and out to present the JSON payload to the application makes it faster for insert. For small JSON document the speedup resulting from lookup might be marginal when running JSN_EXTRACT. Building the index entry is just done once so access through the index will not have to redo the JSON_EXTRACT.

What I really like about this addition of the JSON format to MySQL is that we are able to have an hybrid approach mixing tabular data with JSON column. Contrary to JSON only, schema less datastore like MongoDB this hybrid data store might turn out to be the best choice for a wide range of application and might please developers.

Leave a comment






× seven = 14