Using MySQL to save and query JSON documents

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';
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).

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_database
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

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’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.

--

--

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

Love podcasts or audiobooks? Learn on the go with our new app.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
OneBitAhead

OneBitAhead

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