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!




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