JSON data is a wonderful way to store data without needing a schema but what about when you have to yank that data out of the database and apply some sort of formatting to that data? Well, then you need JSON_TABLE.
JSON_TABLE takes free form JSON data and applies some formatting to it. For this example we will use the world_x sample database’s countryinfo table. What is desired is the name of the country and the year of independence but only for the years after 1992. Sound like a SQL query against JSON data, right? Well that is exactly what we are doing.
We tell the MySQL server that we are going to take the $.Name and $.IndepYear key’s values from the JSON formatted doc column in the table, format them into a string and a integer respectively, and alias the key value’s name to a table column name that we can use for qualifiers in an SQL statement.
mysql> select country_name, IndyYear from countryinfo,
json_table(doc,"$" columns (country_name char(20) path "$.Name",
IndyYear int path "$.IndepYear")) as stuff
where IndyYear > 1992;
+----------------+----------+
| country_name | IndyYear |
+----------------+----------+
| Czech Republic | 1993 |
| Eritrea | 1993 |
| Palau | 1994 |
| Slovakia | 1993 |
+----------------+----------+
4 rows in set, 67 warnings (0.00 sec)
mysql>
So what else can JSON_TABLE do? How about default values for missing values? Or checking that a key exists in a document. More on that next time. For now if you want to try MySQL 8.0.3 with JSON_TABLES, you need to head to Labs.MySQL.COM to test this experimental feature.
via Planet MySQL
JSON_TABLE