Using MySQL to save and query JSON documents

It does not necessarily need a major release to add NoSQL-like functionality to a relational database management system. MySQL 5.7.12 introduced to X Plugin without a buzz. It enables you to create, read, update and delete JSON documents without enforcing a schema to the data. Tooling support includes a MySQL Shell for JavaScript and Python as well as connectors for NodeJS, Java, and .NET.

Ever since NoSQL databases have entered the stage there were attempts to extend MySQL to make it more NoSQL-ish. That attempts aimed at either the performance or JSON capabilities, e.g. the HandlerSocket plugin bypassed much of the SQL-related engine overhead to speed up operations and LIB_MYSQLUDF_JSON introduced a set of functions to output the result of and SELECT query as JSON.

All efforts led to native support of a JSON data type, JSON functions (which both were added in MySQL 5.7.8) and finally the X plugin. The plugin introduces a new protocol connectors and tool can use to communicate with the server.

Installation

mysql> INSTALL PLUGIN mysqlx SONAME 'mysqlx.so';

or mysql.dll on Windows respectively. After that, to see that you succeeded check for the X Plugin in

mysql> SHOW PLUGINS;

The Document Store

Unless you import the sample data from the MySQL website which gives you an idea of what a Document Store can contain, you will of course have to create your own collections. Let’s do a quick example. It will also show how working with the new tools differ from traditional MySQL clients.

MySQL Shell

mysqlsh -u username your_database

Once logged in you will notice the prompt is mysql-js> and not mysql>. From that point on the commands are more like JavaScript code and less SQL-ish:

mysql-js> db = session.getSchema("your_database");
<Schema:your_database>
mysql-js> db.createCollection("YourCollection");
<Collection:YourCollection>
mysql-js> db.YourCollection.add({_id: "oba", name: "OneBitAhead"})
Query OK, 1 item affected
mysql-js> db.YourCollection.find("_id = 'oba'")
[{_id: "oba", name: "OneBitAhead"}]
1 document in set

The exploration of manipulation functions like add(), find(), modify(), and remove() is up to you. Go have a look at the docs for further information. We recommend a special look at the query syntax available for the find() operation with arbitrary data.

Combining both the Document and the SQL world

mysql-js> db.TableA.insert("id", "name") values ("oba", "OneBitAhead");mysql-js> db.TableA.select(["id", "name"]).where("id = 'oba'");
1 row in set

That means you cannot know if you are accessing a table or collection by its name, so be careful choosing your operation to execute on. It also means that names of collections and tables within a database must not clash.

One step further a MySQL table can mix relational data and JSON thanks to the native JSON datatype. The same rules for querying JSON as above apply in that cases.

That’s it for now

When you are interested in a thorough article on using the Document Store in an app, like connecting to NodeJS, send us a message.

Thanks for reading. Go crazy with the Document Store! Best way to stay tuned is following us on Twitter. Comments are very welcome.

The tech staff of OneBitAhead GmbH, putting the web stack to work. Here to discuss daily bits & bytes. #javascript #nodejs #webcomponents #rdbms #php

The tech staff of OneBitAhead GmbH, putting the web stack to work. Here to discuss daily bits & bytes. #javascript #nodejs #webcomponents #rdbms #php