MySQL 8.0: From SQL Tables to JSON Documents (and back again)

One of the nice things about MySQL 8.0 is the ability to combine the relational and document models. As a developer, you can choose to work with traditional SQL tables, with schemaless JSON documents, or with both types at the same time. Here we show how to bridge SQL tables and JSON documents by using JSON functions. Enjoy!

First, let us create a simple SQL Table “employees” and insert some values:

And then we verify table content:

Ok, good. Nothing new so far.

From an SQL Table to a JSON Document

Here we use two JSON aggregation functions called JSON_ARRAYAGG() and JSON_OBJECT(). We simply select from employees and convert the result set into JSON like this:

We now have a JSON Document referenced by the variable @jsonempl. Let us look at it using the JSON_PRETTY() function:

Nice!

We then drop the existing “employees” table:

From a JSON Document to an SQL Table

Here we use the JSON table function called JSON_TABLE(). This function creates an SQL view on the JSON Document, i.e. a mapping between JSON and SQL. We then wrap a CREATE TABLE employees AS around it like this:

Again, we verify table content:

Voilà , we are back where we started !

That’s it for now, and thank you for using MySQL!

About Geir Hoydalsvik

Geir Høydalsvik has been working with MySQL Database team since 2008. He is currently employed by Oracle, based in Norway. He is Senior Software Development Director and responsible for the development and maintenance of MySQL Database. He has a background in the database industry, working for the database startup company Clustra Inc. on the Clustra database and for Sun Microsystems on Java DB. He has a Master degree in Computer Science and a PhD in Software Engineering from the Norwegian University of Science and Technology.

One thought on “MySQL 8.0: From SQL Tables to JSON Documents (and back again)

Leave a Reply