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
The plugin is not installed by default but fortunately that only requires (MySQL >= 5.7.12 and) a one liner using the MySQL Client:
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
Your JSON documents will be stored in collections. Think of collections as containers that allow indexes on the JSON data in it. A collection is addressed by client tools with a unique name in a schema (meaning database).
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
A good start for accessing JSON is the MySQL Shell. It gives you a new command line based client program named mysqlsh. For starters the login is quite old-fashioned
mysqlsh -u username your_databaseOnce 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 affectedmysql-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
It’s important to know that the MySQL Shell can also access relational tables with db.getTables() and modify it’s contents with select(), insert(), update(), and delete() methods. This approach is the best of both worlds - MySQL Shell and the well-known SQL client. As you will see, accessing a table looks the same like accessing a collection:
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
This short introduction showed how to use MySQL as a Document Store and access it with the MySQL Shell. All contents are mainly based on the docs.
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.
