Hello again, today we wanted to comment on the changes that have been made in MySQL for the use of JSON data types within the MySQL tables themselves.
As we already discussed in this post: https://www.gpsos.es/2018/05/tipo-de-datos-json-en-mysql/
We have already seen this functionality above and today we want to expand it.
Examples with JSON data types in MySQL
To explain the JSON data types we are going to create a normal table like the following one and fill it with data:
Create table continents (id int, continent varchar (30), population bigint); insert into continents values (1, 'Asia', 4581757408); insert into continents values (2, 'Africa', 1216130000); insert into continents values (3, 'Europe', 738849000); insert into continents values (4, 'America', 1001559000); insert into continents values (5, 'Oceania', 38304000); insert into continents values (6, 'Antarctica', 1106) ;.
In the first test, we are going to create a new table in which we include a JSON type.
CREATE TABLE continents_json ( id int, json test );
In this new table, we are going to insert the data from the continents table but already with the JSON format itself, for this we are going to use the json_object function:
mysql> insert into continents_json select id, json_object ('Continent', continent, 'Population', population) from continents;
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> select * from continents_json;
+------+----------------------------------------------------+
| id | test |
+------+----------------------------------------------------+
| 1 | {"Population": 4581757408, "Continent": "Asia"} |
| 2 | {"Population": 1216130000, "Continent": "Africa"} |
| 3 | {"Population": 738849000, "Continent": "Europe"} |
| 4 | {"Population": 1001559000, "Continent": "America"} |
| 5 | {"Population": 38304000, "Continent": "Oceania"} |
| 6 | {"Population": 1106, "Continent": "Antarctica"} |
+------+----------------------------------------------------+
6 rows in set (0.00 sec)
As you can see, we have created a JSON Object for each of the rows. Now we have the name and population field integrated into the test field of the new table.
We can also create arrays of JSON objects and store them in the same field as above. This complicates the search and data processing a bit but gives us greater flexibility in storing the data. To show how it works, we are going to do the INSERT in the same table. We simply add the function JSON_ARRAYAGG which allows us to add the 6 JSON objects in a single array:
mysql> truncate table continents_json;
Query OK, 0 rows affected (0.04 sec)
mysql> insert into continents_json select 1, JSON_ARRAYAGG (json_object ('Id', id, 'Continent', continent, 'Population', population)) from continents;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> select * from continents_json;
+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | test |
+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | [{"Id": 1, "Population": 4581757408, "Continent": "Asia"}, {"Id": 2, "Population": 1216130000, "Continent": "Africa"}, {"Id": 3, "Population": 738849000, "Continent": "Europe"}, {"Id": 4, "Population": 1001559000, "Continent": "America"}, {"Id": 5, "Population": 38304000, "Continent": "Oceania"}, {"Id": 6, "Population": 1106, "Continent": "Antarctica"}] |
+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
We hope that the entry is of interest to you and that you can use it. If you want us to help you with any MySQL query or any database or you want us to review your environment. Do not hesitate to contact us.
See you in next posts.
Greetings,
DBA team.
If you do not want to miss the next entry or the ones we publish, you can register in our newsletter monthly. With one email a month you will be up to date with all our publications.
Still do not know Query Performance ? Find out how it can help you in your Oracle environment. More information on their page LinkedIn .
Follow GPS on LinkedIn