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:
|
1
2
3
4
|
mysql> CREATE TABLE employees(id INT PRIMARY KEY, name VARCHAR(45), age INT); mysql> INSERT INTO employees(id, name, age) VALUES (1,'John', 34); mysql> INSERT INTO employees(id, name, age) VALUES (2,'Mary', 40); mysql> INSERT INTO employees(id, name, age) VALUES (3,'Mike', 44); |
And then we verify table content:
|
1
2
3
4
5
6
7
8
9
|
mysql> SELECT * FROM employees; +------+------+------+ | id | name | age | +------+------+------+ | 1 | John | 34 | | 2 | Mary | 40 | | 3 | Mike | 44 | +------+------+------+ Query OK, 0 rows affected (0,00 sec) |
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:
|
1
2
3
4
|
mysql> SET @jsonempl=(SELECT JSON_ARRAYAGG( -> JSON_OBJECT("id", id, "name", name, "age", age)) -> FROM employees); Query OK, 0 rows affected (0,00 sec) |
We now have a JSON Document referenced by the variable @jsonempl. Let us look at it using the JSON_PRETTY() function:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
mysql>SELECT JSON_PRETTY(@jsonempl); +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | JSON_PRETTY(@jsonempl) | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | [ { "id": 1, "age": 34, "name": "John" }, { "id": 2, "age": 40, "name": "Mary" }, { "id": 3, "age": 44, "name": "Mike" } ] | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0,00 sec) |
Nice!
We then drop the existing “employees” table:
|
1
2
|
mysql>DROP TABLE employees; Query OK, 0 rows affected (0,00 sec) |
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:
|
1
2
3
4
5
6
7
|
mysql> CREATE TABLE employees AS -> SELECT * FROM JSON_TABLE(@jsonempl, "$[*]" -> COLUMNS (id INT PATH "$.id", -> name VARCHAR(45) PATH "$.name", -> age INT PATH "$.age")) emps; Query OK, 3 rows affected (0,00 sec) Records: 3 Duplicates: 0 Warnings: 0 |
Again, we verify table content:
|
1
2
3
4
5
6
7
8
9
|
mysql> SELECT * FROM employees; +------+------+------+ | id | name | age | +------+------+------+ | 1 | John | 34 | | 2 | Mary | 40 | | 3 | Mike | 44 | +------+------+------+ 3 rows in set (0,00 sec) |
Voilà , we are back where we started !
That’s it for now, and thank you for using MySQL!